1
Thanks

A few words of thanks would be greatly appreciated.

Excel - Formula based on the color of cell

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)

Photo: © Everypixel

1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Excel - Formula based on the color of cell », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).