Excel – How to conditional format based on cell color

microsoft excelmicrosoft-excel-2016

I have excel 2016 I am trying to conditionally format adjacent cells based on the color of the cell i.e. if B5 is green then A5 should become green. Is this possible? I'm not concerned at this time about the contents of the cell just the color.

Best Answer

This can be accomplished with a user-defined function. First, create the function to identify the fill color of the cell:

Function IdentifyColor(CellToTest As range)

'Returns R + (256 * G) + (65536 * B)
'IdentifyColor = 255 for red, 65280 for green, etc.
IdentifyColor = CellToTest.Interior.Color

End Function

Then create a conditional format formula for cell A5 based on this formula: =IdentifyColor(B5)=65280

It may be easier to use HEX2DEC to specify colors. Therefore, the formula would become: =IdentifyColor(B5)=HEX2DEC("00FF00")

`Screenshot of Conditional Formatting Rule

If the color of cell B5 is green (RGB value 0, 255, 0), the formula returns true and the format of cell A5 will change to how you specify.

You may need to press F9 after changing the formatting to force recalculation and make sure the conditional formatting is applied.

Related Question