If date has passed and another cell is blank, turn blank cell red

[craigm88]

Hi, I'd appreciate any help with 3 conditional formatting issues I'm trying to work out please

If the date in cell H has passed and there's a date (any date) in cell I then turn it green

If the date in cell H has passed and the cell in column I is blank, change the blank cell red

If the date in cell H is within the next week, change the blank cell and the date cell to orange

I know I'm asking a lot but I've googled so much and I can't find the solution

Thanks in advance

 

[Anthony47]

Select I2:I1000 (or a suitable range) and apply conditional format using the following 3 formulas:

=(I2<>"")

Assign "No any format"

=(H2<=(TODAY()+7))*(H2<>"")

Assign interior color = Orange

=(H2<>"")*(I2="")

Assign interior color = Red

Make sure the tre conditions are evaluated in that sequence; you may use UP or DOWN arrows (5) to move each condition to the proper level

Check Interrupt if true (4) for each of the conditions

Please look ath the image to identify the elements I refer to