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.

Hi, When you entered your formula referring to the cell with "Cancelled"

did you enter it as say C7 or as $C$7If 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"

Regards

Oh! That's the problem!!

Thanks!

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....

Hi, 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.

Regards

If you select a range e.g. A1:A14 and enter a formula in Conditional Formatting field, such as: =IF(B1>0,TRUE,FALSE)

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?

Hi,

Fantastic!! Problems cleared!!

Thanks a lot for your help!!

Best regards

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History