Automatic conditional formatting from a list

Hi All

I need a macro that colours cells if the product name in column A on it's 
row matches any in the product list.

There is a list of product names in C17:End(xlUp).Row, that have different 
backgrounds applied to each cell in the list.

The macro looks at Column A in the worksheet & compares it to the product 
name list.

Where it finds a product name match, it goes across to columns I:BJ (on the 
matched row) and applies the product name's background colour to any "active 
cells" (i.e. value >0).

The following code does this but it's applies the background to the same 
range as it's searching, and that data is a column - not a row...
How do I adapt it?

FYI
Z = product list starting from row 74 in the old code and already formatted 
in different backgrounds
My range is C17:End(xlUp)

C = is the data being searched and having the background applied too - but 
it's a column.
My range to search is column "A44:A200"
My range to apply the background too is "matched row between I:BJ".

Currently it's a Worksheet_SelectionChange type macro.
Will this make the file run slow? i.e. will it need to re-calculate & 
re-apply alot?

If yes, can it be a normal macro where the user clicks the button to update 
the cell formats on all rows within a range (e.g. 44:200) - but it will have 
to clear the backgrounds before running each time.

______________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Integer
Dim Z As Integer

For Z = 74 To Cells(Rows.Count, "Z").End(xlUp).Row
For Cell = 95 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(Cell, "C").Value Like "*" & Range("Z" & Z).Value & "*" Then
Cells(Cell, "C").Interior.Color = Range("Z" & Z).Interior.Color
End If
Next Cell
Next Z
End Sub

-- 
Thank for your help in advance
BeSmart
0
Utf
3/5/2010 11:42:01 AM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
1038 Views

Similar Articles

[PageSpeed] 27

Reply:

Similar Artilces:

Conditional formatting in text boxes
I have textboxes on a userform. The text boxes read (and write) to cells A1:A60 Adjacent to these cells I have a comments column which is filled in for particular rows Depending on whether the adjacent cell in column B has an entry, is there a way to make the textboxes change their font attributes? For example, become BOLD when there is a comment. Can anyone help? You should have posted your code..Within your loop add a line something like the below.. Dim intCount as Integer For intCount = 1 To 60 Me.Controls("Textbox" & intCount).Font.Bold = _ No...

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

Format Cells #9
Is there any way to prevent text from extending into adjacent blank cells? (as the default setting) Hi use 'Format - Cells - alignment' and check 'word wrap' -- Regards Frank Kabel Frankfurt, Germany "KPK" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:130ec01c4434e$26cd8610$a101280a@phx.gbl... > Is there any way to prevent text from extending into > adjacent blank cells? (as the default setting) We actually do not want the text to wrap, we just want it to truncate. Thanks, Kieran >-----Original Message----- >Hi >use &#...

Comment formatting
How do I set the default format for comments? See this page for all info about comments http://www.contextures.com/xlcomments01.html Maybe ? http://www.contextures.com/xlcomments02.html#Default -- Regards Ron de Bruin http://www.rondebruin.nl "TonyL" <TonyL@discussions.microsoft.com> wrote in message news:C2F9F095-CF0F-4A03-83CE-B01F934E4826@microsoft.com... > How do I set the default format for comments? Thanks Ron that was easy!!! "Ron de Bruin" wrote: > See this page for all info about comments > http://www.contextures.com/xlcomments01.html &...

How to create my own generic list?
I'm still new at C# or any OO program and my coworkers are all at a seminar this week. How do I create my own "generic list" that implements all the features of the .net generic list<string> plus a method to fill itself from strings in an external file. Is there a simple template somewhere I could start with? Do I just inherit from generic list and add my method? thanks, LJB "LJB" <postmaster@127.0.0.1> wrote in message news:O%23EBRGuuKHA.1796@TK2MSFTNGP02.phx.gbl... > I'm still new at C# or any OO program and my coworkers a...

IF Multiple Conditions
A B C D E Color Total Time Total Time 2 Time Allowed Y/NO BLUE 1:22:33 2:22:33 1:00:00 BLUE 2:22:33 3:22:33 2:00:00 BLUE 3:22:33 4:22:33 3:00:00 BLUE 4:22:33 5:22:33 4:00:00 BLUE 5:22:33 6:22:33 5:00:00 RED 6:22:33 7:22:33 6:00:00 RED 7:22:33 8:22:33 7:00:00 RED 8:22:33 9:22:33 8:00:00 I need an IF formula that: - will read IF condition in the A column is Blue it will bring back...

vbs with conditions
Hello there, I need to create a vbs script that will do the following: Will check if certain application is installed, by checking if the following key is exists: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\visio viewer_v2003 If exist, the scripts ends. If not exist, will install an application from a network share: \\servername\sharename\setup.exe Any help will be appreciated. Thank you Amir :) "Amir" <Amir@discussions.microsoft.com> wrote in message news:BE109F90-96B8-4E17-937A-45672F638395@microsoft.com... &...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

Count unique occurrences in a list
Hi guys, I have a list as so: Part1 Run1 Part1 Run1 Part2 Run1 Part3 Run1 Part4 Run2 Part4 Run2 Part1 Run3 Part2 Run3 Part5 Run3 Part6 Run4 Part1 Run5 Part1 Run5 Part1 Run5 Part6 Run5 .... Part1 Run1001 Part3 Run1001 Part3 Run1001 .... The number of unique parts may be >100 and the number of runs can be anything (it's actually ~2000 in my current list) What I would like to do is this: For each Part, count how many Runs contained that Part (that is the number of unique runs containing at least of that Part.) I should end up with a new list similar ...

Modifying Distribution lists
All our users are now able to modify any distribution list. Even if a manager is set, it doesn't matter. How can I get this fixed? Exchange server 2003. Thanks Somehow, your users have gotten Active Directory admin rights to the distribution groups. I would start with the simplest approach. Check the membership of your Domain Admins, Administrators, and Account Operators groups and see who is a member. You could then look at the individual security properties on the groups and see if they have been modified and allow authenticated users to write to them. -- Jim McBee B...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

Distribuiton list sending from worng e-mail address
I am using Outlook 2000 (its old but it works), it is not connected to an exchange server. Everytime I make a distribution list it use the wrong e-mail address for the sender address, but I don't get offered the opportunity to enter an e- mail address. I have two e-mail address setup in Outlook, one I use all the time and the other one which I use about twice every 6 months and to use it I have to go into Tools > Services to change profile if I want to send and receive from said e-mail address. So why is the distribution list sending from the wrong address?? Tim...

Conditional Formatting #43
Looks easy doesn't it? Hah! Any instructions as to how I can do the following: Column M cells are already formatted to show the date as (in the cas of M2 for example) =A2+28 (so, 28 days after the date that is entere into A2). I want the date that is calculated & shown in M2 to turn re 7 days before that date. If the date in A2 is 01/06/04 then M2 wil read 29/06/04 but I want it to turn red on 22/06/04 (I use dd/mm/yy a I'm in Europe) and then I want it to turn bold red two days before th date - in this example 27/06/04. I also need a similar effect to make the date generated in co...

Automatic updates, automatically disabling itself
Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

Mail format problems Exchange 2003
I am running OL2002 on Win XP Pro on both a desktop and laptop in a large network. The network was recently converted from NT Server to Exchange Server 2003. At that time, all of my mail began being converted from plain text to html. This appears to be only on both machines accessing my account, not on other accounts in the network. If I send a message in plain text mode, it is received by others as plain text, but converted to html in my Sent folder. Plain text sent to me is converted to html upon receipt. Since this happens on both machines, I think it is something to do with the serve...

Column format to override row fomat in FrX
In FrX the row format overrides the column format for font styles eg. bold etc. it would be useful if it was possible to have the option for the column format to override the row fomat ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesss...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

Loss of formatting when saved as .xls file
I have created a document in Excel 2007 with some normal cell formatting, such as bold text, merge & center, and border lines. When checked for compatibility no errors or messages appear. When the file is saved as an .xls file, closed, then opened again all of the formatting is completely gone and all that is left is text. I had this problem with another file but it was fixable becuase it did show compatibility issues that could be fixed. Any help with this problem would be greatly appreciated. ...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Automatic Forward
How can I automatically forward messages received from a specific sender to another's e-mail person? Thanks, ...

CE6: How to Disable SIP popup automatically
Hi, I know SIP popup can be disabled by the following registry setting in CE5: [HKEY_CURRENT_USER\ControlPanel\Sip] "TurnOffAutoDeploy"=dword:1 But It doesn't work in CE6. How to disable SIP auto-Popup in CE6? Thanks. Eric The SIP Auto Deploy in CE is due to Windows Controls invoke SHSipPreference (exported by AYGSHELL.DLL). So the easiest way is to avoid including AGYSHELL (SYSGEN_AYGSHELL) But if excluding AYGSHELL is not an option, you may need to modify the SHSipPreference (PRIVATE\SHELL\SHELLPSL\HAVEAYGSHELL\shellpsl.cpp) to supress SIP s...