fill (color) only unfilled (uncolored) cells

Topic

New Reply

Couldn’t find an answer with search, so my apologies if this is answered elsewhere already. Is there a simple (or not so simple) way to color cells that are currently unfilled (may or may not have data, but the cells are uncolored)? I have sheets that have several sections already color-coded, but I want to change the color of all of the remaining uncolored cells from white (unfilled) to another more attractive neutral tone, without affecting the cells already colored.

Reply | Quote

Replies

You can use conditional formatting for this – see the recent thread conditional formatting to detect nulls.

Reply | Quote

I have attached a file for you to view using the =ISBLANK(B2) formula as the conditioning format. Highlight the table of figures and click Format—> Conditonal Formatting to see how it works

Reply | Quote

I have attached a file for you to view using the =ISBLANK(B2) formula as the conditioning format. Highlight the table of figures and click Format—> Conditonal Formatting to see how it works

I understand (at least I think I do) conditional formatting and the =ISBLANK formula. But my issue is how to make non-colored cells a different color even if they have data already. Using your attachment, how would I make the background color of all the white cells, say, tan without changing the color of the green ones? Actually, I can do that with your sample since it only has two colors and two conditions (blank/not blank). But I have several different sets of data color-coded grey, blue, magenta, red, etc. I just want to change the white ones, and ONLY the white ones to another color. Maybe I didn’t explain it well the first time. Or maybe I’m missing the obvious solution. Is there a conditional format formula that says something to the effect of, if cell color is A, make cell color B? Except I don’t really want a conditional format; I just want to change an existing format…without changing all the others. And without doing it manually. Really more like a find/replace scenario. Tried that too, but can’t figure out a way to find based only on cell color.

Reply | Quote

Got you…I think, so will it be numerical values that will be inputted?

1 < X <10 Grey
10< X<20 Blue
20<X<30 Green
30<X<40 Yellow

etc

Reply | Quote

Add this code to the workbook section of your file in VBE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set RangeToFormat = Sheets(“Sheet1”).Range(“B2:G4”)
For Each cell In RangeToFormat
With cell
‘ Empty cells
‘If IsEmpty(cell) Then
‘.Interior.ColorIndex = xlNone
‘ Numeric cells
If IsNumeric(cell.Value) Then
Select Case cell.Value
Case 1 To 10
.Interior.ColorIndex = 38
Case 11 To 20
.Interior.ColorIndex = 40
Case 21 To 30
.Interior.ColorIndex = 36
Case 31 To 40
.Interior.ColorIndex = 35
Case 41 To 50
.Interior.ColorIndex = 34
End Select
‘ Error cells
ElseIf IsError(cell.Value) Then

‘Error cells
.Interior.Color = 3
‘ Other cells (text)
Else
.Interior.ColorIndex = xlNone
End If
End With
Next cell
End Sub

Reply | Quote