I’ve been struggling to get Conditional Formatting to accommodate this:
If C2 , D2 and E2 all contain the letter "O" then the font for all should be green.
If any of them are changed to "P" then the font for those cells still containing "O" should now become red.
For those now containing “P” the font should become blue.
I’d be grateful for any assistance with this. I can get so far with it but can’t find the way for it to be completely working.
Last edited by CDandVinyl; 11-04-2020 at 10:53 PM.
Using Excel , Word and Access 2003 - For the whole of 2024
Conditional formatting is not as powerful in 2003 as it is in 2007 forward. I implemented it in a modern version and got a warning when I back-converted it but I think it will work. However, I cannot show you how to re-create it from scratch in 2003 because the rules will look different. And I haven't used 2003 for over 10 years. There are probably a handful of people who do Excel consulting professionally that keep around an old version for questions like this.
Conditional formatting doesn't know anything about the order you do things, so it cannot understand "changed from P to O". The logic I used was state analysis:
If all cells are O then they are all green
If at least one cell is P, and all of the ones that are not P are O, then the P cells will be blue and the O cells will be red. (This requires two separate rules.)
If there is any other combination, then there is no color for any of the cells.
These rules don't care about what order you do things. If you start with three O cells (all green), change one of them to P (P becomes blue, two O cells become red), then delete an O cell, they will all lose their color. In a situation like that, if you want the P cell to stay blue as long it contains a P, no matter what else happens, then you need VBA.
First, set defaul color is "red"
Conditional formatting formula:
=C2="P"
set as "blue"
=COUNTIF($C2:$E2,"O")=3
set as "green"
Quang PT
Hi Guys
Ok thanks. As it sometimes happens , these things end up being more complicated than they first appear!
I can see that it does work in 2003 in the examples you gave so I'm really grateful for these working solutions.
Last edited by CDandVinyl; 11-04-2020 at 11:08 PM.
Green =COUNTIF($C$2:$E$2,"O")=3
Blue =(SEARCH("P",$C2&$D2&$E2)+2)=COLUMN(C2)
Red =(SEARCH("P",$C2&$D2&$E2)+2)<>COLUMN(C2)
Ben Van Johnson
Glad I was able to help, thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks