Highlight contractors without a finish time?

[GHI1968]

I would like to highlight any contractors who do not have a finish time each day.
I have a column for start time, finish time and one to calculate the hours worked. (3 adjacent colums going left to right)
Ideally as soon as a start time is entered it would highlight the 3 cells for the employee red and when a finish time is entered it shows the hours worked and changes the 3 cells to green.
Can anyone help please?

 

[Scott T]

Excel 2010
	A	B	C	D
1	name	start	finish	hours
2	Contractor1	7:30
3	Contractor2	8:00	13:00
4	Contractor3	7:00
5	Contractor4	8:30	15:00
6	Contractor5	7:00	10:00
7	Contractor6	7:15	14:30
8	Contractor7	6:30
Sheet1

Select the cells for example from above B2:D8.

Select conditional formatting
Select New Rule
Select Use a Formula to determine which cells to format
use this formula

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

Select your format and select OK

 

[GHI1968]

That works great. Thank you Scott T.

Is there a way to add 'xxx' to the cells in C that are highlighted red?

 

[Scott T]

Conditional formatting can not change the contents of the cell only the format. You would need a formula in the cells. When you enter a finish time it will overwrite the formula. The formula used to conditional format posted above checks if the cell is empty since it is no longer empty you will need to change the formula

[=AND($B2<>"",$C2="xxx")/CODE]


[RANGE=cls:xl2bb-100][XR][XH=cs:5]Excel 2010[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][/XR][XR][XH]1[/XH][XD=h:l]name[/XD][XD=h:l]start[/XD][XD=h:l]finish[/XD][XD=h:l]hours[/XD][/XR][XR][XH]2[/XH][XD=h:l]Contractor1[/XD][XD=h:r]7:30[/XD][XD=h:l|cls:fx][FORMULA==IF(B2&lt;&gt;&quot;&quot;,&quot;xxx&quot;,&quot;&quot;)]xxx[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA==IF(C2=&quot;xxx&quot;,&quot;xxx&quot;,C2-B2)]xxx[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:l]Contractor2[/XD][XD=h:r]8:00[/XD][XD=h:r]13:00[/XD][XD=h:r|cls:fx][FORMULA==IF(C3=&quot;xxx&quot;,&quot;xxx&quot;,C3-B3)]5:00[/FORMULA][/XD][/XR][XR][XH]4[/XH][XD=h:l]Contractor3[/XD][XD=h:r]7:00[/XD][XD=h:l|cls:fx][FORMULA==IF(B4&lt;&gt;&quot;&quot;,&quot;xxx&quot;,&quot;&quot;)]xxx[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA==IF(C4=&quot;xxx&quot;,&quot;xxx&quot;,C4-B4)]xxx[/FORMULA][/XD][/XR][XR][XH]5[/XH][XD=h:l]Contractor4[/XD][XD=h:r]8:30[/XD][XD=h:r]15:00[/XD][XD=h:r|cls:fx][FORMULA==IF(C5=&quot;xxx&quot;,&quot;xxx&quot;,C5-B5)]6:30[/FORMULA][/XD][/XR][XR][XH]6[/XH][XD=h:l]Contractor5[/XD][XD=h:r]7:00[/XD][XD=h:r]10:00[/XD][XD=h:r|cls:fx][FORMULA==IF(C6=&quot;xxx&quot;,&quot;xxx&quot;,C6-B6)]3:00[/FORMULA][/XD][/XR][XR][XH]7[/XH][XD=h:l]Contractor6[/XD][XD=h:r]7:15[/XD][XD=h:r]14:30[/XD][XD=h:r|cls:fx][FORMULA==IF(C7=&quot;xxx&quot;,&quot;xxx&quot;,C7-B7)]7:15[/FORMULA][/XD][/XR][XR][XH]8[/XH][XD=h:l]Contractor7[/XD][XD=h:r]6:30[/XD][XD=h:l|cls:fx][FORMULA==IF(B8&lt;&gt;&quot;&quot;,&quot;xxx&quot;,&quot;&quot;)]xxx[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA==IF(C8=&quot;xxx&quot;,&quot;xxx&quot;,C8-B8)]xxx[/FORMULA][/XD][/XR][XR][XH=cs:5][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]C4[/XD][XD]=IF(B4<>"","xxx","")[/XD][/XR][XR][XD]C2[/XD][XD]=IF(B2<>"","xxx","")[/XD][/XR][XR][XD]C8[/XD][XD]=IF(B8<>"","xxx","")[/XD][/XR][XR][XD]D2[/XD][XD]=IF(C2="xxx","xxx",C2-B2)[/XD][/XR][XR][XD]D3[/XD][XD]=IF(C3="xxx","xxx",C3-B3)[/XD][/XR][XR][XD]D4[/XD][XD]=IF(C4="xxx","xxx",C4-B4)[/XD][/XR][XR][XD]D5[/XD][XD]=IF(C5="xxx","xxx",C5-B5)[/XD][/XR][XR][XD]D6[/XD][XD]=IF(C6="xxx","xxx",C6-B6)[/XD][/XR][XR][XD]D7[/XD][XD]=IF(C7="xxx","xxx",C7-B7)[/XD][/XR][XR][XD]D8[/XD][XD]=IF(C8="xxx","xxx",C8-B8)[/XD][/XR][/RANGE]