Use color in an if statement

chamblessw

chamblessw

New member
Joined
Mar 10, 2013
Messages
7
Reaction score
0
Points
0
I want to write =If(Sheet1!A2=Yellow,0,Sheet1!A2). That is, I am copying data from sheet 1 to sheet 2. I want to be able to invalidate a few cells by manually coloring them. Therefore, I want all cells that I manually color in sheet1 to show 0 in the new cell. Conversely if sheet1!A2 is not colored and contains a numeric value of 6, I want the new cell to show 6.
 
You cannot use just a formula to check the colour of a cell.

You can use a VBA UDF though.

If you hit CTRL+F11 and then INSERT|MODULE, paste the following code in the editor

Code: 
Function CellColor(CColor As Range) As Integer
    CellColor = CColor.Interior.ColorIndex
End Function

Then back in the sheet you need to use something like:

=If(CellColor(Sheet1!A2)=6,0,Sheet1!A2)

where 6 refers to interior color code for Yellow.

To find out the colour code you can use =CellColor(A2) this will tell you the number associated with the colour, then you can use that in your IF conditional check.

One thing to note with this option is, that if you change the colour in Sheet1!A2, the formula will not update. You will need to go the formula and re-activate and hit enter.
 
Your micro worked great. I found a problem. I can detect yellow cells and set a copy in another cell to zero. That was my goal. Unfortunately, it appears that the equation with the micro requires refreshing. That is, if I color another cell after the macro is in place, the micro formula does not respond. If I remove the yellow color, the micro does not respond. I can only get a response if I change the color and then rewrite the macro or paste the macro. That is, it appears the micro formula above must be refreshed. It does not respond as a simple formula. I am I experiencing what you would expect?
 
I did mention that in the last line of my post:

One thing to note with this option is, that if you change the colour in Sheet1!A2, the formula will not update. You will need to go the formula and re-activate and hit enter.
Click to expand...

You can add an Application.Volatile True line to the code, which will autochange the result, but with something like color choosing (or data validation), I think you need to use F9 to re-evaluate or after some other physical keyboard entry change occurs.


Code: 
Function CellColor(CColor As Range) As Integer
    Application.Volatile True
    CellColor = CColor.Interior.ColorIndex
End Function

Edit:

One way to "auto recalc" is to add a Worksheet_SelectionChange event macro:

right click on the sheet tab, then select View Code. Paste the following:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

Now in the sheet, once you change a color and hit enter, the formula should update.
 
Last edited:
You're welcome. Notice that I just added an Edit to my last thread, at same time as you posted your last post.
 
You must log in or register to reply here.
Back
Top