conditional formatting help needed

diverdave

Board Regular
Joined
Dec 3, 2002
Messages
92
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Dec 3, 2002
Messages
92
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Revise formula to:

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

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top