Forum Discussion
Conditional Formatting if date is past And Cell is Blank
TemiT20 Hello! This formula was also helpful for me. I am hoping you have time to help me with a modification? Is there another variable I can add to restrict it to read only the row that it is in? I have values in a table, but for what is blank beneath it, it still highlights all blank cells. I tried the formula below, but that is just highlighting my header row for reasons I can't explain.
=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)
beccasme what is your formula doing?
=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)I tried your formula and applied it to range
=$A$2:$J$20and it works well in my sheet.
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
