Excel - Calculating if the cell has a fill color

[RWWAMS]

Hello everyone,

What I want to do seems very simple. All I want is to enter a formula that says basically the following:

If the cell fill color is not white then assign a
value of 15 to the cell.

Simple, right?

Any feedback, formulas, functions or ideas would be great! Thanks.

 

[VBAjedi]

Hello!

Amazingly, Excel does not have an easy way to do this. You will need to use VBA code. You could attach the following code to a button to process the whole sheet at once:

Dim Cell As Range
For Each Cell In Range("A1:C500&quot ' Use your range here
If Cell.Interior.ColorIndex > 0 Then
' Note: White = 2, No background = -4142
Cell.Value = "15"
End If
Next Cell


If you would rather it check automatically every time a cell on your worksheet changes, place the following code in the Worksheet_Change event for your worksheet:

If Target.Interior.ColorIndex > 0 Then
' Note: White = 2, No background = -4142
Cell.Value = "15"
End If

Both examples assume you want to put this value in any cell that has ANY color background (including WHITE or BLACK). Only cells with a clear background will be ignored.

Let me know if you need assistance setting this up!


VBAjedi

 

[RWWAMS]

Thanks for replying!

I’m not too experienced with VBA, but I seem to have gotten the concept working. I’ve got a macro (through a button) that changes the fill color of all highlighted cells. I added the code that adds the value 15 to the highlighted range (which you were kind enough to give me). This works like a charm!

The only problem is that the range and the value “15” are hard coded. Is there anyway to avoid this?

The range would be whatever cells are highlighted, and the value to assign to each of the highlighted cells would be in the first column of the highlighted row (Column A, Row ?). For example, if I highlighted the range “C4:K4”, the cell containing the value to populate the range would be found in “A4”.

Any help would be great. Thanks!

 

[Zathras]

See my code in your other post: thread707-498960

I think it is exactly what you are asking for.