Color Formatting in Excel 2007

Microsoft Excel 2007
February 12, 2010 at 07:32:48
Specs: Windows XP
I refer to an old post (posted at September 24, 2009 at 02:21:42) regarding the captioned.

I have an excel file where I originally have different colors for different columns to represent different data. However, if a specific cell fulfills a condition, say "Cancelled" (I already have a drop down, providing options like "Normal" and "Cancelled", for this), I would like the whole row to be changed to gray.

I followed the instructions given in that old post:

1) Select the whole of a row
2) Choose "Conditional Formatting", "Manage Rules", "New Rules", "Use a Formula to Determine......"
3) Enter the formula "=IF($K$10="Cancelled",TRUE,FALSE)
4) Set the format (gray fill)
5) Click "OK"

However, only the first cell of the row had its color changed to gray. I wonder if I did anything wrong. Or is it not possible for the conditional formatting to work because I have different colours for the same row?

Thanks for your help in advance.

See More: Color Formatting in Excel 2007

February 12, 2010 at 07:54:45

When you entered your formula referring to the cell with "Cancelled"
did you enter it as say C7 or as $C$7

If you entered it as C7, the first cell in the row checks C7 for "Cancelled" but the second cell in the row checks D7 and so on.

If you use $C$7 all cells in the row check C7 for "Cancelled"


Report •

February 12, 2010 at 08:08:07
Oh! That's the problem!!
But how could I apply the "conditional formatting" to other rows all in one go?
That means the determining cell for row D should be $D$7 and $E$7 for row E etc....

Report •

February 12, 2010 at 08:29:39

If you select several rows, and use $D7
the first row will use D7 all along the row
the second row will use D8 and so on.

Its just like putting a formula in the first cell and then dragging it to other cells, except that conditional formatting does it automatically.

The formula will change if there is no $ sign before the row or column, but will not change if there is a $ before row or column.


Report •

Related Solutions

February 12, 2010 at 08:32:57
If you select a range e.g. A1:A14 and enter a formula in Conditional Formatting field, such as:


the CF feature will update the "B1" for all cells in the selected range.

In other words, A3 will be dependent on B3, A11 on B11, etc.

If you select that same range and enter:

=IF($B$1>0,TRUE,FALSE) then all cells in the range will be dependent on B1.

Does that help?

Report •

February 12, 2010 at 08:34:28
Fantastic!! Problems cleared!!
Thanks a lot for your help!!
Best regards

Report •

Ask Question