Forum Discussion
dtulips
Jul 24, 2023Copper Contributor
Help with conditional formatting (dates)
Hello! I want to set a conditional formatting rule where if the date in cell A is "past due" and cell B is empty, then cell B is highlighted. I also tried to do if the date in cell A is "past...
mtarler
Silver Contributor
let's say you want to apply the conditional formatting to B2:B100 then highlight those cell and go to 'create new rule' then select 'use custom formula' then in the formula box use a formula like:
=($A2<TODAY() )*ISBLANK($B2)
then set the formatting you want in cells B2:B100
for the other option select Cells A2:A100 (for example) and use the exact same formula but set the formatting (text color) you want.
=($A2<TODAY() )*ISBLANK($B2)
then set the formatting you want in cells B2:B100
for the other option select Cells A2:A100 (for example) and use the exact same formula but set the formatting (text color) you want.
dtulips
Jul 25, 2023Copper Contributor
mtarlerthank you! I sort of got it! If I apply the formula to one row it works fine, but when i try to apply it to all the cells in that column it wont work.
this is what I used: =($E$8:$E$38<TODAY() )*ISBLANK($F$8:$F$38)
is this not correct?
- mtarlerJul 25, 2023Silver Contributoryou want to apply the formula as if it is explicitly for ONLY the top left most cell in the applied to range and use the absolute indicator ($) or not as if you were going to copy or fill the rest of the range. So in my example above I used $A2 and $B2 because for A2 (or B2) I want to use the cells A2 and B2 but as you go down you want excel to apply the formula to that corresponding row (hence NO $ in front of the row) and I did include the $ in front of columns A and B but since the applied to is only 1 column that really doesn't matter.