Kioskea
Recherche
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



Issue


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>

Solution


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"       
              
     Else       
         dispColorIndex = "NO"       
                
    End If       

End Function


As per your example, in B1 enter:
=dispColorIndex(A1)

Note


Thanks to rizvisa1 for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-formula-based-on-the-color-of-cell.pdf

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 (en.kioskea.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.