Need to write a formula based on color of cel [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I need help on creating formula in excel 2007.

Situatiion is .... Say cell A1 is coloured as 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 the colour of the cell A1 is anyother or none.

Please suggest...

Thanks & Regards
Raj

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
If you are looking for a formula, I am not sure if there is any inbuild excel formula to do that. You can create your own function to do that



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)
I added this formula in Excel 2007 and it has one issue: it doesn't automatically update the formula, I have to either close/open the file or to enter manually in the cell with F2 in order for the cell to update. do you have any idea it is doing like this?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
color change would not trigger any event (at least i dont think it does), so depends how and why color change might help you. also try adding
application.volatile

this will cause the function to recalc, if there is any change in the cells