Excel - Formula based on the color of cell

Ask a question
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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team