Clear cell contents on all tabs in Workbook based on cell color

[rach_oh]

Hi,

Trying to clear the contents of all cells that are Color #20 or RGB 204/255/255 that are on almost all tabs in various cells (manual colored)

Private Sub CommandButton3_Click()
Dim xcell As Range
Dim xrng As Range
Set xrng = ActiveWorkbook.Sheets
For Each xcell In xrng
If xcell.Interior.Color = RGB(204, 255, 255) Then
xcell.ClearContents
End If
Next
End Sub

 

[DanteAmor]

Hi @rach_oh
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Private Sub CommandButton3_Click()
  Dim sh As Worksheet
  Dim f As Range, r As Range
  Dim cell As String
  
  For Each sh In Sheets
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = RGB(204, 255, 255)
    Set r = sh.UsedRange
    Set f = r.Find("", , xlValues, xlPart, , , False, , SearchFormat:=True)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        f.ClearContents
        Set f = r.Find("", f, xlValues, xlPart, , , False, , SearchFormat:=True)
      Loop Until cell = f.Address
    End If
  Next
  Application.FindFormat.Clear
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[rach_oh]

This worked perfect - thank you!!!!

 

[rach_oh]

I just ran another module and got this error from the code

 

[DanteAmor]

I just ran another module and got this error from the code

What do you mean by "another module"?

What changed in your book or on your sheet?

You could check what you had in your sheets in the execution of post #3 and now what you have in your sheets in the execution of post #4.