Display error if range does not have fill color

Question

I am trying to create a code for checking filling color of the cells. If there is no color, code should continue forward. If there is a fill color then code should give an error.

I have an error handler, it is sending an email message on error:

    On Error GoTo ErrorHandlerColor

ErrorHandlerColor:

Here is my code:

With ThisWorkbook.Worksheets("Main").Range("A2" & ThisWorkbook.Worksheets("Main").Range("L2").End(xlDown).Row)
    If IsNull(.DisplayFormat.Interior.ColorIndex) Then
    Else
      MsgBox 1 / 0
      Exit Sub

          End If
    End With

My range is actually something A2:L1343, but as it is changing I am setting it till the last row.

By error I mean MsgBox 1 / 0 so logic does not work

Currently the problem is that it is giving an error all the time. Even if there are no cells with filling color. What can be possibly wrong?

`.ColorIndex = xlNone` would tell you if a cell has shading, but that's only really reliable on a single cell — Tim Williams, Nov 11 '19 at 19:31
`ThisWorkbook.Worksheets("Main").Range("L2").End(xlDown).Row)` returns a row, so you end up with something like `.Range("A2" & ` whereas you will want `.Range("A2:L" & ` — cybernetic.nomad, Nov 11 '19 at 19:35

Siddharth Rout · Accepted Answer · 2019-11-11T20:00:51.697

To find the last row, see This

As @TimWilliams mentioned that .ColorIndex = xlNone is only really reliable on a single cell. So use .ColorIndex = xlColorIndexNone. Is this what you are trying? This doesn't need a loop.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Worksheets("Main")

    With ws
        lRow = .Range("L" & .Rows.Count).End(xlUp).Row

        If .Range("A2:L" & lRow).Interior.ColorIndex = xlColorIndexNone Then
            MsgBox "There is no color"
        Else
            MsgBox "There is color"
        End If
    End With
End Sub

Or

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Worksheets("Main")

    With ws
        lRow = .Range("L" & .Rows.Count).End(xlUp).Row

        If .Range("A2:L" & lRow).DisplayFormat.Interior.ColorIndex = xlColorIndexNone Then
            MsgBox "There is no color"
        Else
            MsgBox "There is color"
        End If
    End With
End Sub

Re the point about `xlNone` vs `xlColorIndexNone` - both are named constants, both have the value -4142, so logically there is no difference in using one or the other. (that said, `xlColorIndexNone` is a member of `Excel.XlColorIndex` so it's the contextually "correct" one to use). — chris neilsen, Nov 11 '19 at 20:49
Interesting to note: Tim's point about _`.ColorIndex = xlNone` is only really reliable on a single cell_ relates to the fact that it will return a color value only if all cells in the range have the same color (which may be xlNone / xlColorIndexNone). If there are more than one colors in the range, it returns `Null`. (Which is why your no loop code works) — chris neilsen, Nov 11 '19 at 20:49

Vityata · Answer 2 · 2019-11-11T21:29:30.367

1

There are very few things in VBA, that are Null - and these are query results from a database server. Thus, the IsNull() would always be false. (Null values for variables in VBA)

If you try the below code on an empty worksheet, it will show how to check for a no color in a cell with Range("A1").Interior.ColorIndex = xlColorIndexNone:

Sub TestMe()

    With Worksheets(1).Range("A1")
        'True, because it is w/o color
        Debug.Print CBool(.Interior.ColorIndex = xlColorIndexNone)
        'always False
        Debug.Print IsNull(.Interior.Color)
        'changing interior to red
        .Interior.Color = vbRed
        'False, because it is red
        Debug.Print CBool(.Interior.ColorIndex = xlColorIndexNone)
         'always False
        Debug.Print IsNull(.Interior.Color)
    End With

End Sub

edited Nov 11 '19 at 21:29

answered Nov 11 '19 at 19:35

Vityata

42,633
8
55
100

Filled with White != no fill – chris neilsen Nov 11 '19 at 20:10
@chrisneilsen - I miss your point. If I open a new Excel and write `?activecell.Interior.Color = 256 ^ 3 - 1` in the immediate window, it gives true. – Vityata Nov 11 '19 at 20:15
1

While what you say is true, it misses the point. OP is looking for cells to be set to No Fill. Your test does not distinguish between No Fill and Filled With White. You need ColorIndex to do that. – chris neilsen Nov 11 '19 at 20:30

Display error if range does not have fill color

2 Answers2