Previous Topic Next Topic

Need to write a formula based on color of cel

Solved/Closed
Raj - Feb 26, 2010 at 08:52 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 17, 2010 at 01:06 PM
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
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 26, 2010 at 01:47 PM
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)
0
silviu
Sep 29, 2010 at 10:07 AM
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 17, 2010 at 01:06 PM
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
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses