Excel - Formula based on the color of cell

October 2016

The Excel Office Software allows defined functions to be executed in the Excel Worksheets by a user. Instead of a formula based on the color of a cell, it is better to write a function that can detect the color of the cell and manipulate the data accordingly. Some knowledge of programming concepts such as if-else conditions and looping may be useful to write user defined functions for inclusion in Excel worksheets. To write a function to determine the color of a cell, the Interior.Color object can be used. Therefore, it is better to write a function instead of a formula based on the color of the cell for operations.


I need to create a formula in Excel.
Suppose cell A1 is colored Red - I need to put a formula in cell B1, where the result should be "Yes" if the color of cell A1 is Red, and "No", if cell A1 is another color or no color.
I need help creating this formula in Excel 2007. </gras>


If you are looking for a formula, I am not sure if there is an inbuilt Excel formula existing already that can do this; but you can create your own function to do it.

Public Function dispColorIndex(targetCell As Range) As Variant       

Dim colorIndex As Long       

    colorIndex = targetCell.Interior.Color       
    If (colorIndex = 255) Then       
       dispColorIndex = "YES"       
         dispColorIndex = "NO"       
    End If       

End Function

As per your example, in B1 enter:


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Formula based on the color of cell » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.