How to merge / mix colors of 2 conditonal formatted matrices in the 3rd matrix?

[Deleted]

I'm dealing with this problem in the last couple of days.

I have formatted with Conditional formatting two color matrices 52x24 cells in two different sheets and both sheets (matrix results) are linked to the first numerical data base sheet.

What I want is to generate the third matrix which is a mix (merge) of previously defined 2 matrices, and it should be defined by the combination of their colors (regardless the numerical content).

For example: if the cell A1 sheet1 is RED and the cell A1 sheet2 is YELLOW then the cell A1 on the sheet3 is BLUE; or if the cell A1 sheet1/sheet2 is GREEN, the cell A1 sheet3 is GREEN too and so on, using 12 colors to generate the 3rd matrix with the final result, based on the color combination rules.

Is there any simple or a complex way in solving this issue?

What I'm trying is to define a formula in the 3rd matrix conditional format rules but I'm not sure if it is a good approach.

Thanks in advance for your help and your time.

[#2]

Hi,

This way that doesn't work. The color is the property of the cell and formulas work with value of the cell. To work with colors such way that's only using VBA.

But you may combine your rules for the Sheet3 based on numeric values of the cells in Sheet1 and Sheet2.

[#3]

Thank you for your answer.

Yes, I guess that is the end limit of the Conditional formatting possibilities, and in this case it will not work, because I'm combining different incompatible numerical values - sheet1 is temperature and sheet2 is a coeficient based on one other formula. At the end I'm trying to group results as a color combination of their limits.

Well in that case, VBA is the only way to get further in this case.

Any help in this forum will be appreciated.

Thank you one more time.

[#4]

In general that's the limit of formulas, not of conditional formatting. On the other hand if you already have rules in Sheet1 and Sheet2 the only what you need is combine them in one formula for the Sheet3 with AND condition.

If, for example your temperature rule

A1 is RED <if A1 >=30>

and coefficent rule

A1 is YELLOW <if A1 <0.5>

when the BLUE rule for Sheet3

=(Sheet1!A1>=30)*(Sheet2!A1 < 0.5)

[#5]

Thank you very much, Sergei !

It's impressive, effective and more easier solution than I was trying to figure out.

I'm now testing and works perfectly for this case. I simply have never used before and empty cell with combining formulas to get the color match, and Conditional Formatting continues to work great in this case.

Thanks one more time and all the best !

[#6]

You are welcome