Excel formatting

Question

Hello,

I am fairly new at the advanced functions of Excel and I was wondering if anyone out there might be able to show me what formula I need to use to achieve my end goal.

What I have is a job cost vs job revenue sheet that I will be tracking throughout the year. What I will do is that I input my contract amount into the cells in the "B" column. Then over in the "E" column, I have my total job costs, which for some jobs (as they are not completed yet) will have no data put in their cells.

What I am trying to achieve, since I will put my contract amount in the corresponding cell in the "B" column as soon as we get the contract signed, is to have the Cell in column E and corresponding row (for example B2 and E2) get highlighted yellow if it is empty.

So my end result would be something like if I have a contract amount in B2 and a job cost in E2,. then E2 will NOT be highlighted, and if I have a contract amount in B3 but no job cost in E3, then E3 will be highlighted yellow.

Thanks in advance for all your guys' help!

Answer 1

Select E2:E1000 (or however far down you want).

E2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND($B2<>"",$E2="")

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.