Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jun 05, 2018

vba to detect if there is a colored cell in a range

How do I write the code for the following scenario?

 

If range("A2:A1001") has at least one colored cell Then

   do > clear the color for that range ( this line I think I can do the code)

End If

 

many thanks

excel
Macros and VBA
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Jun 06, 2018

    Mr Rothstein helped me with the syntax that I needed (the bold letters below) and I am sharing with anyone who might need this.

    many thanks

     

    Private Sub CommandButton8_Click()

    If Range("A2:A1001").Interior.ColorIndex = xlColorIndexNone Then
    MsgBox "Nothing to clear...", vbExclamation
    Range("A2").Select
    Exit Sub
    End If

    MsgBox "This will clear the Hi-lighted rows..", vbInformation
    x = Range("Z2")
    ActiveSheet.UNPROTECT password:="PSWD"
    Range("A2:J" & x).Select
    With Selection.Interior
      .Pattern = xlNone
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    ActiveSheet.Protect password:="PSWD"

    End Sub

     

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Jun 05, 2018

    in relation to this topic,

    How do I count in vba the number of colored cell (just one particular color - say red) in a range say A2:A1001?

    many thanks

Resources