Conditional Formatting- - colour coding

Hi Sophie

The trick to making this work is to apply a formula based conditional format and then check to see if the absolute reference in the formula needs to be amended to allow you to apply the format across all rows of your list.

For example: In a row that has three cells as follows:

A1 B1 C1
OK 9 1/3/2013

We might want the conditional formatting for A1 to A3 go green when the status in A1 is OK.

Highlight Cells A1 to C1

From the Home ribbon, click Conditional Formatting
From the menu that appears choose New rule
From the dialogue box that opens click the bottom option:
"Use a formula to determine which cells to format"

Click into the the box labelled
"Format values where this formula is true"

Click into cell A1 as this contains the value that will trigger the formatting.
The formula will appear in the dialogue box.

=$A$1

Complete it as follows
=$A$1="OK"

The formula automatically has dollar symbols included. In other words it assumes the references must be absolute.
This is not helpful for us as this format will be copied to other rows. Remove the dollar symbol before the reference to row 1.
=$A1="OK"

Lastly click the format button to decide the formatting that should apply if the condition is true. You might decide to set the pattern to be green and the Font to White and Bold so it stands out.

Click OK and you should now find that the cells A1 to C1 go green with white text when A1 contains the text OK. With anything else in A1 the text reverts back to normal.

Now Click on one of the cells that contain this new conditional format rule (either A1, B1 or C1) and click the format painter button on the left side of the Home Ribbon (looks like paint brush). You can now "paint" this conditional format over any other records in your database (e.g. A2 through to C2, A3 to C3 and so on).

Because we removed the dollar symbol from before the row number in the Conditional formula Excel will test the contents of the current row in column A, not just the first row.


I hope this helps. I realise this is quite a long answer - do let me know if you have any questions.


Kind regards,
Andrew