Formating cell color based on cell color

[kyleno]

I am trying to conditionally format a cell based on other
conditional formatting in a range of cells.

Ex.
I would like cell E3 to be:
Red if any cells (O3, T3, Y3, AD3, AI3, AN3, AS3) are Red
IF NOT THEN
Yellow if any cells (O3, T3, Y3, AD3, AI3, AN3, AS3) are Yellow
IF NOT THEN
Green if any cells (O3, T3, Y3, AD3, AI3, AN3, AS3) are Green
IF NOT THEN
Blue if any cells (O3, T3, Y3, AD3, AI3, AN3, AS3) are Blue


However I did find a work around on Stackoverflow that started me in the right direction:


On the Excel Ribbon, go to "Formulas" and click on "Name Manager". Select "New" and then enter "CellColor" as the "Name". Jump down to the "Refers to" part and enter the following:
=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),1,1))


Hit OK then close the "Name Manager" window.

Now, in cell A1 enter the following:
=IF(CellColor=3,"FQS",IF(CellColor=6,"SM",""))


This will return FQS for red and SM for yellow. For any other color the cell will remain blank.

***If the value in A1 doesn't update, hit 'F9' on your keyboard to force Excel to update the calculations at any point (or if the color in B2 ever changes).

Below is a reference for a list of cell fill colors (there are 56 available) if you ever want to expand things: Color Palette and the 56 Excel ColorIndex Colors Excel

Any help?

 

[DRSteele]

I might be off-base here, but I think this method only works for certain colours. Would it not be easier to just apply conditional formatting to a cell (like E3) using the same TRUE/FALSE formula that triggers the Red/Yellow/Green/Blue conditionals in the first place?

 

[kyleno]

I ended up creating a separate condition to fill the cells in with specific words and then have the conditional format trigger when it sees the particular word. It worked quite nicely.

In each of the cells I used:
Column O
=IF(AND((ISBLANK(L3)),TODAY()>=$M$21),"Follow Up",IF(L3>M3,"Past Due",IF(L3="","Blank",IF(L3<>"","Complete"))))

And THEN conditionally formatted them:
Conditional Formatting
Cell Value = Follow Up
Cell Value = Past Due
Cell Value = Blank
Cell Value = Complete

THEN for my overall status (column E) which was the initial column that I wanted to reformat when the color changed I used the following conditional formatting
Red: =OR(O3="Follow UP",T3="Follow UP",Y3="Follow UP",AD3="Follow UP",AI3="Follow UP",AN3="Follow UP",AS3="Follow UP")
Yellow: =OR(O3="Past Due",T3="Past Due",Y3="Past Due",AD3="Past Due",AI3="Past Due",AN3="Past Due",AS3="Past Due")
Green: =OR(O3="Blank",T3="Blank",Y3="Blank",AD3="Blank",AI3="Blank",AN3="Blank",AS3="Blank")
Blue: =OR(O3="Complete",T3="Complete",Y3="Complete",AD3="Complete",AI3="Complete",AN3="Complete",AS3="Complete")

Still a bit of a workaround but it gets the job done.