Forum Discussion
Conditional Formatting if date is past And Cell is Blank
OliverScheurich Hi! Something seems to not work correctly. When I enter a value in A or J, the row above highlights, instead of the row that should meet the condition. Or even if J is blank, it highlighted the middle row. (See attached screen shot)
I was also defining by entire rows because the table is intended to grow over time, instead of giving it a definite end row value. When I do that, it highlights the header. I'm not sure what I'm doing wrong.
The rule for conditional formatting and the applied range must start in the same row.
In the attached example i have the rule:
=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)
and the applied range:
=$2:$50
The applied range can easily be adjusted to e.g. =$2:$5000 but always keep in mind that rule and applied range start in the same row.
The other rule in attached sheet is:
=$H2>TODAY()
and the applied range is:
=$2:$50