Forum Discussion
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 ...
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