How to use Excel color cell if formula

How to use Excel color cell if formula

Excel allows defined functions to be executed in 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. To write a function to determine the color of a cell, the interior color object can be used.

Example issue

Suppose cell A1 is colored Red and you ask for a formula to locate 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 has no color.

Solution

If you are looking for a formula, there isn't 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"       
Else       
         dispColorIndex = "NO"       
End If       
End Function

In B1 enter:

=dispColorIndex(A1)

Need more help with Excel? Check out our forum!

Excel