Forum Discussion
Solved
Conditional Formatting multiple rules
I am trying to conditionally format a cell that is green for dates in the future (from today), red for dates in the past (before today) and black for two criteria, 1) date in the past and 2) when a c...
I think the attached version does what you want, with 5 rules, one for each color (black, green, light blue, amber and red).
How your data is structured? Date is one cell and "complete" (whatever) is in another and which one, or what?
SergeiBaklan Thanks for getting back to me. Date is in one cell, with status in another cell. I have several rules in place, which might be complicating things. Trying to get the conditional formatting in the column next to the status column. The red cells should be black because the date is in the past the column next to it reads 'completed'. Does that make sense?
In addition, much better to follow "one rule = one format". In your case first three could be combined as
=AND( G13 <> "In progress", E13>= $A$1) + AND( G13 <> "Not started", E13> $A$1 )or like for the green.
- Thanks for this, I have applied the above formula and it works but I still can't get the black RAG to work, using the completed status.
You have multiple rules that set the fill color.
Excel will apply only the first rule (from the top down) that evaluates to TRUE.
In your screenshot, that is the rule with formula =AND(G13<>"At Risk",E13<$A$1)
The rules below that don't matter, so the cells are colored red.
HansVogelaar - thanks for this, I hadn't realised that there was a logic structure. This is what I now have, which isn't fully working.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?