Conditional Formatting

[TH123]

Hi all,

Would like to get your support to solve below issue. Thank you in advance

Condition

1. As long as the date in row 1 less than today => fill in grey
2. If the date in row 1 = today => fill in blue
3. If the date in row 1 > today & data is not blank => fill in Green

Result by myself
I've tried as capture but fail due to:

• Column AB: Today is 12/28 but 12/27 is fill in blue (correct should be grey)
• Column AF:12/21 is the date in future but fill in grey (correct should be green)
• Column AD, AE: some cell have data itself but haven't been fill yet & some cell have blank but fill in green

 

[GraH]

Most likely you have a wrong formula, meaning the reference is incorrect for the given selection.

Please test this way:

- Select cell A1 and open the conditional formatting. What's the formula now?
- It should be =A$1<TODAY( ) for the first rule.
I'm guessing you will find another reference.

 

[TH123]

Most likely you have a wrong formula, meaning the reference is incorrect for the given selection.

Please test this way:

- Select cell A1 and open the conditional formatting. What's the formula now?
- It should be =A$1<TODAY( ) for the first rule.
I'm guessing you will find another reference.

Hi GraH,

Thanks for your advice. I've corrected following your comments & all are correct now. Actually A1 is blank but from this case, can learn that all formular s/b start from begining of reference area.

Thank you again

 

[GraH]

Thanks for the feedback @TH123. It's a common mistake and I made it a lot in the past.
Actually, when you make the conditional formatting with relative or mixed references, it is best to only refer to the active cell you started from to make the rule, in both the rule formula and in the applied range. Save the rule and then update it to the full range you want the CF to work on. It's the best way I found to avoid the mistake.