Ask a question »

Excel - Formula based on the color of cell

March 2015

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.

Formula based on the color of cell


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.
For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by aakai1056. - Latest update by Jeff
This document entitled « Excel - Formula based on the color of cell » from Kioskea ( 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.