VBA IF any cell in a column is a specific colour THEN...

[bgrice]

Hi. I am currently writing code that contains several ElseIf statements. If a statement is true it then presents a msgbox. The macro will be triggered by the click of a button.

How would I write the code to check whether any cell in given range is a specified colour - in this case G16:G500 and RGB 225, 199, 206. Thanks.

 

[BSALV]

Sub color()
     For Each c In Range("G16:G500").Cells                      'loop through cells
          b = c.Interior.color = RGB(225, 199, 206)             'flag, that color found
          If b Then Exit For                                    'if color found, stop seeking
     Next

     MsgBox "there are " & IIf(b, "", "no ") & "cells with that color"     'add the word "no" in case of 0 colored cells

End Sub

 

[bgrice]

Thank you. But how might I write that into an ElseIf line?

Also I have not come across b and c before so would I need to Dim those?

Apologies for not understanding but I'm relatively new to vba although I seem to be getting my head around most things. Thanks.

 

[BSALV]

Sub color()
     Dim c As Range, b As Boolean

     For Each c In Range("G16:G500").Cells                      'loop through cells
          If c.Interior.color = RGB(225, 199, 206) Then         'flag, that color found
               b = True
               Exit For                                         'if color found, stop seeking
          End If
     Next

     If b Then
          MsgBox "there are cells with that color"              'add the word "no" in case of 0 colored cells
     Else
          MsgBox "there are no cells with that color"           'add the word "no" in case of 0 colored cells
     End If
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider which uses no loops at all...

Sub Clear_BoldItems()
  Dim ColorCell As Range
  With Application
    .FindFormat.Clear
    .FindFormat.Interior.Color = RGB(225, 199, 206)
    Set ColorCell = Range("G16:G500").Find("", SearchFormat:=True)
    If ColorCell Is Nothing Then
      MsgBox "There are no cells with that color"
    Else
      MsgBox "There is at least one cell with that color"
    End If
    .FindFormat.Clear
  End With
End Sub

 

[bgrice]

Thank you, both. I appreciate your work on this and the solutions you've put forward, I just can't seem to get them to fit with what I'm doing. I don't seem be able to amend either of the solutions to fit the next ElseIf statement nor do I appear to be able to enter them as separate macros and call from an ElseIf e.g. ElseIf Call color

Perhaps I should have articulated the problem better or pasted my code earlier, which is now below. Any ideas? Thank you.

Sub FinishErrorsTest()
    If IsEmpty(Range("C3")) Then
        MsgBox "No manufacturer selected"
    ElseIf IsEmpty(Range("C4")) Then
        MsgBox "No Proposed implementation date selected"
    ElseIf WorksheetFunction.CountA(Range("A16:G500")) = 0 Then
        MsgBox "No product selected"
    'Here is where I want to enter the next ElseIf statement on colour and for _
    it to stop searching the range if the colour is found and then present the msgbox
End Sub

 

[bgrice]

Sorry, I think I have discovered an issue: The colour is set from conditional formatting and running code in the immediate window I can see that the interior colour of the cell is not what I specified so I think the conditional formatting is not helping. Will have to rethink this. Apologies for wasting your time.