Conditional Formatting based on cell value

[Cliffd]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I would like to be able to use conditional formatting so that I can make a simple change to an entire row in a spreadsheet depending on a value in a cell taken from a data validation list, but can't seem to get it working.

For example, the data validation list will contain entries such as 'in progress', 'completed' or 'cancelled'. Depending which value I choose to use in the cell, I want the entire row to be coloured (e.g. red, orange, green). I'm sure I've seen this done but I can't seem to get the formula right in the conditional formatting drop down menu.

I'd be grateful for any help or advice.

Thanks and regards,

Cliff
Brighton, UK

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I would like to be able to use conditional formatting so that I can make a
simple change to an entire row in a spreadsheet depending on a value in a
cell taken from a data validation list, but can't seem to get it working.

For example, the data validation list will contain entries such as 'in
progress', 'completed' or 'cancelled'. Depending which value I choose to use
in the cell, I want the entire row to be coloured (e.g. red, orange, green).
I'm sure I've seen this done but I can't seem to get the formula right in the
conditional formatting drop down menu.

One way:

Say you want to change rows 2:100 based on the value in column D. Then

- Select Rows 2:100, with a cell in row 2 the active cell
- Choose Format/Conditional Formatting... and set the
dropdowns and input box to read

CF1: Formula is =$D2 = "in progress"
Format1: <pattern>/<red>

CF2: Formula is =$D2 = "completed"
Format2: <pattern>/<orange>

CF3: Formula is =$D2 = "cancelled"
Format3: <pattern>/<green>

XL will adjust the relative portion of the formula (i.e., '2'), but
retain the absolute ('$D').

 

[BMR]

J. E. McGimpsey thank you! You just saved me a lot of time and effort!

 

[Cliffd]

Thank you for your prompt reply and for making it so easy to understand and implement. Works great.