conditional formatting help needed

[diverdave]

What I have is a daily report for a motel. Guest’s names are put into column B and their room charge is put into column D on the same row.

To make sure the night auditor is accounting for every rented room they have to either enter the guest name in Column B or put a V (for vacant) in the cell to acknowledge that they have checked to see if the room was occupied.

A common mistake is to enter the guest name but forget to enter the room charge in column D.

What I want to happen is for column D to change color only if anything except V is entered into column B of the same row to remind them to enter the room charge.

Then I want the color to in column D to disappear when they enter the room charge in that column.

Thanks,
Dave

 

[NBVC]

Select the range in Column D and go to Format|Conditional Formatting

Select Formula Is from 1st drop down and enter

=And($B2<>"V",$B2<>"") assuming your selection begins at D2. If not match corresponding row in the formula.

Click Format and choose colour pattern

Click Ok twice.

 

[diverdave]

thanks NBVC - still not exactly as I need

Thanks for the reply. When I enter the name in column B, column D does highlight however, when I enter the dollar amount in column D the format color does not disappear.

 

[NBVC]

Revise formula to:

=And($B2<>"V",$B2<>"",$D2="")

 

[diverdave]

NBVC - THANK YOU!!!

Thanks - that works perfectly. Much appreciated.
Dave