January 10th, 2012, 12:39 PM
Excel Conditional Formatting Question
I have a spreadsheet with the following conditional format rules (as formulas):
These work fine if the cell in Column G matches Cancelled, Hold or Complete, but I would like to change it so that if any cell in column G contains (not matches) those keywords anywhere in that cell that the whole row changes color. I have tried many variations of find/search/iserror with no luck. Please help
=(INDIRECT("G"&ROW())="Cancelled") //Applies to $A:$G and Turns Row Red
=(INDIRECT("G"&ROW())="Hold") //Applies to $A:$G and Turns Row Amber
=(INDIRECT("G"&ROW())="Complete") //Applies to $A:$G and Turns Row Green
Last edited by Nullified; January 10th, 2012 at 12:43 PM.
January 12th, 2012, 09:38 AM
Which version of Excel and are you using VBA script or just cell formulas?
January 17th, 2012, 01:18 PM
I could easily tell you how to do this with VBA, but as far as doing it strictly with a worksheet formula I don't have any ideas.
“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
January 17th, 2012, 02:25 PM
I am using Excel 2007. I was using just formulas, but I didn't even think about using visual basic. I guess I could do that, if you could give me a heads up on a proper macro function.
January 18th, 2012, 09:01 AM
Here is a great page for detailing the colors of the backgrounds and text and how to do it. Otherwise you can do something as simple as what I have or throw it into a loop. Google will be your best friend for many of those commands.
If range("G3").value = find("hold") Then
range("A3:F3").cells.interior.colorindex = 3
Else if range("g3").value = find("complete") Then
range("A3:F3").cells.interior.colorindex = 4