Check colour of entire column

Question

I'm trying to create an if statement that checks the colour of column B.

It works if I target a single cell in column B not when I try ("B:B").

This is what I have.

Sub FOO()

    Dim answer As Range

    Set answer = Range("b:b")

    If answer.Interior.Color = vbRed Then

        MsgBox ("There is an issue with column B, please review.")

    End If

End Sub

you cant say that, unless you are checking the whole column is red. Look at FOR loops, both for next and for each. This will help. — Nathan_Sav, Jan 20 '17 at 15:04
Thanks, only thing is I haven't learnt how to do loops yet, the excel guy in work left and I've been tasked with learning VB lol. — Hosey93, Jan 20 '17 at 15:36
You'll use loops all the time, so look at them all do.while while..wend etc. — Nathan_Sav, Jan 20 '17 at 15:38
also, don't use B:B as it's too big, use a defined range, even if it is the entirety. Range("b1:b100) — Nathan_Sav, Jan 20 '17 at 15:39
Thanks for your help, I'm a bit stuck on trying to get it to loop through my defined range though, you're right so far that if another cell in B column is a different color the message box doesn't appear. — Hosey93, Jan 20 '17 at 15:43

cyboashu · Accepted Answer · 2017-01-20T15:43:26.237

As we are getting many quality answers, here is the most optimized code. Fastest, I bet :)

It wont work if you are using one of the ancient versions of excel. anything 2007+ is fine.

Sub OptimizedFOO()

    Dim rngTemp

    With Application.FindFormat.Interior
        .Color = vbRed
    End With

    '/ Sheet1 is example sheet name
    Set rngTemp = Sheet1.Columns(2).Find(What:="", SearchFormat:=True)

    If Not rngTemp Is Nothing Then
          MsgBox ("There is an issue with column B, please review.")
    End If

End Sub

Old answer

Sub FOO()

    Dim answer As Range
    Dim cell   As Range

    '/ This will show message if at least one cell is found with red color

    Set answer = Range("b:b")
    For Each cell In answer.Cells

        If answer.Interior.Color = vbRed Then

            MsgBox ("There is an issue with column B, please review.")
            Exit For

        End If
    Next

End Sub

This is working great now, you're a legend! I'm having to learn VB while at work here and today is my first day. — Hosey93, Jan 20 '17 at 15:48

score 1 · Answer 2 · answered Jan 20 '17 at 15:07

I am not sure, but give you my best guess.

If VB unifies the properties, then it unifies the properties of all the cells of the column. You can then compare the property to a value and this will be True if all rhe proeprties have that (same) value. Otherwise the comparison will be False.

So If answer.Interior.Color = vbRed will be True if all cells have this propety value vbRed. If you want to check if any of the cells have that color, you may need to iterate over all the cells.

I believe VB and the VB object model work like this, but again, I am not sure.

score 0 · Answer 3 · answered Jan 20 '17 at 15:23

0

I would find last used row on column B and than loop through them.

Sub FOO()

    Dim LR As Long, I As Long

    LR = findLastRow("Sheet1", "B")

    For I = 1 To LR
        If Range("B" & I).Interior.Color = vbRed Then

            MsgBox ("There is an issue with column B, please review.")
            Exit For

        End If
    Next I

End Sub

Function findLastRow(shtName As String, colLetter As String) As Long

    With Sheets(shtName)
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            findLastRow = .Cells.Find(What:="*", _
                          After:=.Range(colLetter & "1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            findLastRow = 1
        End If
    End With

End Function

answered Jan 20 '17 at 15:23

ManishChristian

3,759
3
22
50

wouldn't, `Worksheets(shtName).Range(colLetter & worksheets(shtName).Rows.Count).End(xlUp).Row` suffice for findLastRow? – Nathan_Sav Jan 20 '17 at 15:36
If Searching for value, why not search for the color itself. Killshot. :) – cyboashu Jan 20 '17 at 15:45
@Nathan_Sav, I avoid finding last row that way. Check [this](http://stackoverflow.com/a/11169920/1652222) answer for more details. – ManishChristian Jan 20 '17 at 16:02
@cyboashu that's not a bad idea. I see that you have already "optimized" your answer. ;) – ManishChristian Jan 20 '17 at 16:02
nps, that's why I use the last cell of the sheet, not range, then use up not down. :) – Nathan_Sav Jan 20 '17 at 16:16

Nathan_Sav · Answer 4 · 2017-01-20T15:52:27.460

You could do something using the autofilter, something like this

Function AnyRedCells(rngRangeToInspect As Excel.Range) As Boolean

Application.ScreenUpdating = False
rngRangeToInspect.AutoFilter
rngRangeToInspect.AutoFilter field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
'   Using >1 as assuming header on column
AnyRedCells = (ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count > 1)
rngRangeToInspect.AutoFilter
Application.ScreenUpdating = True

End Function

Used like so

Sub OptimizedFOO2()
    If AnyRedCells(Range("b23:b26")) Then
          MsgBox ("There is an issue with column B, please review.")
    End If
End Sub

Check colour of entire column

4 Answers4