May 12 2020 09:34 AM
Hello,
Am trying to find a formula that works if a date is past today's date and the another cell is blank to highlight the date cell red, until the blank cell is filled.
May 12 2020 10:22 AM
SolutionMay 13 2020 01:35 AM
Feb 17 2022 03:27 PM
@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)
Feb 17 2022 04:40 PM
=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)
I tried your formula and applied it to range
=$A$2:$J$20
and it works well in my sheet.
Feb 17 2022 04:51 PM
@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.
Feb 17 2022 05:27 PM
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
Feb 17 2022 07:21 PM
Feb 21 2022 01:13 PM
Feb 21 2022 01:13 PM
Feb 21 2022 01:14 PM
Jul 07 2022 09:59 AM
Apr 04 2023 07:17 AM
@beccasme what is your formula doing?
May 12 2020 10:22 AM
Solution=IF(AND(a2>TODAY(),b2=""),1,0)
use this formula in conditional formatting