Date Checking custom rules

[Paskylou]

Good afternoon everyone. I have a question and wonder if you could help please. 

Below is an example of a spreadsheet im running 

Basically what I need to do is (ideally) highlight cells A3-G3 if the cell H3 is blank. 

In an ideal world i'd like the cells to be:

yellow if the date in C3 is over 10 days away

red if the date is C3 is less than 4 days away

but once there is a date in H3 they go green.

then, again ideally, if there is a date in H3, but not in C3, then C3 to go Pink.

Is any of that doable? 

thank you! 

p.s. the data is manually input, if that makes any difference?

[#2]

@Paskylou 

Select A3:G100 or however far down you want.

The active cell in the selection should be in row 3, for example A3.

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

=$C3<>""

Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.

Repeat these steps, but with

=$C3>=TODAY()+4

and orange (amber) as color.

Repeat them again, but with

=$C3>=TODAY()+10

and yellow as color.

Finally, repeat them with

=$H3<>""

and green.

Next, select C3:C100 or as far down as you want. C3 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($C3="",$H4<>"")

Click Format...
Select pink as highlight color.
Click OK, then click OK again.

[#3]

Super, thank you so much.

[#4]

@HansVogelaar  sorry another question for you! 

This worked perfectly, you're a star! 

I would like to create a 'key' so people know what the colours mean. Is it possible to do, or do i do it manually? Also, is it possible to have it so it's visable wherever you scroll? I did put it in column A so i could freeze top row and first column, but column A is only visable if i scroll A-Z, but i'd like it visable as i scroll further down the worksheet (row 100+)

any ideas?

[#5]

@Paskylou 

You'll have to create the legend (or key) manually.

You might insert a few empty rows at the top, and create the legend in A1:A5 (fill color as in the conditional formatting rules, description as cell text).

Then freeze the top 5 rows and left column:

[#6]

@HansVogelaar 

Can I apply this kind of formatting to rows as opposed to columns?

Basically I'd like Row 18 cells to turn red should rows 9, 10 or 13 have data in them (columns B-AF) is that something I can do?

[#7]

@Paskylou 

Select B18:AF18.

B18 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

=COUNTA(B$9, B$10, B$13)

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.