Conditional Formatting Formula

[Guest]

I have a spreadsheet that I want to use a formula to conditionally format the
spreadsheet but there are too many ANDs and IFs and I'm getting confused and
I need some help, please.
It's set up like this:

A B C D E
Name Lodging Mileage Other Invoice#

1 Joe Smith 45.00 100.00 9.00 1990
2 Ann Jones 44.00 4444
3 Jesse Smith 43.00 99.00 7777
4 Alfred Jones 77.00 1234

I need a formula that will highlight the row (or even just highlight the
cell containing the name) a certain color when lodging, mileage AND other are
not blank cells, another color when just lodging is not blank cell, another
color when lodging and mileage are not blank cells and another color when
just mileage is not a blank cell.
Can this be done?

Thank you,
Studebaker

 

[Guest]

If you are using XL2003 or earlier, you can only have up to 3 conditional
formats, with a 4th normal format, so to translate your first three
conditions:

Highlight all the cells with the names in - I assume this will be from A2
onwards, with A2 being the active cell. Click on Format | Conditional
Formatting and in the pop-up panel select Formula Is rather than Cell Value
Is, and then enter this formula:

=AND(B2<>"",C2<>"",D2<>"")

then click the Format button on the panel, click on the Patterns tab (for
background colour) and click on the colour you want (eg bright yellow). Then
click OK.

In the CF panel you need to click Add and then we go through a similar
sequence for the second condition:

Formula Is: =AND(B2<>"",C2<>"")

then click the Format button and choose your colour (eg green) and OK.

Click Add once more, and for the third condition:

Formula Is: =B2<>""

then click the Format button and choose your colour (eg pink) and OK. Click
OK once more and you should see these colours displayed in the names cells
where the conditions are met. Be wary that Excel tries to be helpful and
sometimes adds extra quotes in the formulae, so if you do not get the effects
(particularly on the last one), then go back in and edit the condition so
that the formulae are as above. You can use the Format Painter icon to copy
the formats to other name cells if you add more.

If you want to apply the formats across the table, then you will need to
make the cell references partially absolute, by adding a $ symbol before each
letter, eg:

=AND($B2<>"",$C2<>"",$D2<>"")

for the first one.

Hope this helps.

Pete