New Contributor

# Conditional Formatting Dates

So I'm having a complex issue with conditional formatting. If we were to have no issues or a near miss, the cell under the date should be green. If we have a first-aid injury, the cell under the date should be yellow. If we have serious recordable, the cell under the date should be red. The data is pulling from another sheet that has all of the safety incidents separated by near miss, recordable, and first aid.

I had a former co-worker assist me with these formats, but he is no longer here. I'm trying to understand the formula but, I cant quite get it. I tried making my own, but nothing seems to work. Conditional formatting is my weak point in excel. What the best conditional formatting formula to fix this problem? I would like to avoid putting in formats one cell at a time because I have three different charts that are each 30 days that needs formatting.

1/12/2022 Addition: I probably should've added the page the format calculation are coming from.

5 Replies

# Re: Conditional Formatting Dates

=VLOOKUP(A1,\$J\$1:\$K\$31,2,FALSE)="recordable"

Maybe with 3 rules for conditional formatting as shown in the attached file.

# Re: Conditional Formatting Dates

It depends on concrete data layout, it could be like

# Re: Conditional Formatting Dates

Could I highlight the calendar and have this stretch across each date? Also, I added the calculation page.

# Re: Conditional Formatting Dates

I posted the calculations to give a better example.

# Re: Conditional Formatting Dates

=VLOOKUP(A1,Tabelle9!\$A\$2:\$D\$31,4,FALSE)>0

=VLOOKUP(A1,Tabelle9!\$A\$2:\$D\$31,3,FALSE)>0

=VLOOKUP(A1,Tabelle9!\$A\$2:\$D\$31,2,FALSE)>0

Maybe with 6 rules for conditional formatting as shown in the attached file. The calculation page is in sheet "Tabelle9" in my example. 3 rules are applied to range

=\$A\$1:\$G\$1;\$A\$3:\$G\$3;\$A\$5:\$G\$5;\$A\$7:\$G\$7;\$A\$9:\$C\$9

the other 3 rules are applied to range

=\$A\$6:\$G\$6;\$A\$8:\$F\$8;\$A\$10:\$C\$10;\$A\$2:\$G\$2;\$A\$4:\$G\$4