Forum Discussion

Seamus1984's avatar
Seamus1984
Copper Contributor
Jan 26, 2023

Cannot conditionally format based off of another row.

I have a date column in which the date is manually entered. It is currently formatted to turn the text red if equal to or past the due date. This same spreadsheet also has a status column which conta...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 26, 2023

Seamus1984 

Text must be in " " instead of ' '.

You can try

=$F2="Complete"

instead of

=$F2='Complete'

 

Seamus1984's avatar
Seamus1984
Copper Contributor
Jan 27, 2023

OliverScheurich Ok that has allowed the formula to go in but the date is still formatting as red when the status is "Complete".

 

Here is the current arrangement of conditional formats:

I have tried moving the order around and selecting and deselecting 'Stop if True' to see if it will change the font colour but nothing seems to work.

 

Basically I just need the date font to be red if on or past todays date (which is the part that is working) but I want it to change to black font colour once the status in the STATUS column is "Complete".

 

Any help that anyone can give is appreciated.

 

 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 27, 2023

    Seamus1984 

    You can try these rules and "applies to" ranges which return the expected result if i correctly understand what is intended. Attached is my sample workbook. The screenshots show all rules and "applies to" ranges. The difference seem to be the "applies to" ranges and =$F1="Complete" instead of =$F2="Complete" and the hierarchy of =$F1="Complete" and <=$J$1.