Conditional formatting on Excel

Question

I'm making a schedule in excel and need some help with conditional formatting.

I'd like to make it so when people put there names in the orange area the box will turn either green or red depending on whether there name is on the list (in column J) Could someone tell me how I might go about doing this?

Answer 1

Select range $C$3:$H$16

In New Formatting Rule dialog box, choose "use formula to determine which cells to format"

To get green cell, type the following and set fill format to green: =COUNTIF($J$3:$J$9,C3)

To get red cell, type the following and set fill format to red: =AND(C3<>"",COUNTIF($J$3:$J$9,C3)<1)