Excel cell color formatting macro

August 3, 2011 at 17:15:10
Specs: Windows XP

I have an excel spreadsheet in Office 2003. Column A is an alert column. Columns K, U, X, AE, AO, AR and AU have conditional formatting that, dependant on dates entered, change the cell color to either green, amber or red.

What I'm after is a way to change column A color for each row, dependant on the other row colors, in a priority sense. Red is highest, then amber then green. So if any of the cells listed above is red in a row, then column A for that row would be red, regardless of the color of any other cells. If none are red in the row, it would then look for amber, then green. You get the drift?

My excel and VBA skills are not such that I can accomplish this. Any help would be greatly appreciated.

See More: Excel cell color formatting macro

August 3, 2011 at 18:25:36
Why can't you use the same Conditional Formatting rules that turn the other cells in the Row the various colors and apply them to Column A?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 4, 2011 at 14:11:08

Thanks for the reply. I'm not sure how conditional formatting could be used in this way. How could you use it to change one cell in the priority type arrangement I mentioned above?


Report •

August 4, 2011 at 17:02:25
What are the Conditional Formatting rules that you are using in Columns K, U, X, AE, AO, AR and AU?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

August 4, 2011 at 19:30:13

I've managed to get it to do what I want using conditional formatting. As the other cells are all dropdown lists, I could reference the data cell and create formulae around that. Luckily I only had 3 colours to worry about, so 3 conditions. I would still like to find out a way to do this using VBA, as I do want to add a 4th condition, a cell that if it is blank, the formatting above will apply, but if it has a value in it, then the formatting will be ignored.

I appreciate the info so far.

Report •

August 4, 2011 at 20:39:21
The problem with doing what you ask with VBA is that a cell filled with a color via Conditional Formatting is not recognized as being filled by the CF color when checked with VBA.

Try this:

Conditionally Format A1 to be any color except Yellow when it equals 0.

Leave A1 empty and it should turn the color you chose.

Now run this code:

Sub WhatColor()
 MsgBox [A1].Interior.ColorIndex
  [A1].Interior.ColorIndex = 6
 MsgBox [A1].Interior.ColorIndex
End Sub

The first MsgBox should read -4142 which means the cell is not filled.

The second MsgBox should read 6 because the code colored the cell Yellow, even though it's still the same color as before due to the CF.

Run the code again and it'll return 6 both times since it now thinks that the cell is filled with yellow, even though it'll only be yellow when the CF condition is false.

One way to have VBA color a cell to match the color of another cell which was colored by CF is to basically do the same thing you did manually: You have to have VBA check the data in the CF'd cell(s) and fill the desired cell with the matching color.

There are methods for returning the color of a cell filled via CF and I guess you check each cell in your range until you find a Red, if not check them again looking for Yellow, etc. Once a desired color was found, you could color the cell in column A to match.

If you Google something like this, you'll get lots of hits on how to return the color of a cell that was filled via CF:

vba to check color of conditional formatting

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 4, 2011 at 22:09:33
Thanks for the reply and the great explanation, I'm pretty sure I have all the info I need now to move forward on this. I appreciate it.

Report •

Ask Question