Thread: Find cells with specific ColorIndex

Hi there,

How can I change the code to search for cells with specific .Interior.ColorIndex

Sub FindIt()
    Dim strFindMe As String, rngData As Range
    Dim rngFound As Range, firstaddress As String
     
    strFindMe = Interior.ColorIndex = 42    'the entry To  find
     
    With Worksheets("Sheet1")
    
 
        Set rngData = .Range("B1:AF500")
        'the range To search
        Set rngFound = rngData.Find(strFindMe, LookIn:=xlValues)
        If Not rngFound Is Nothing Then
            firstaddress = rngFound.Address
            Do
                With rngFound
                     .Interior.ColorIndex = xlNone
                     .ClearContents
                End With
                'clear data In the matching cell
                Set rngFound = rngData.FindNext(rngFound)
                If rngFound Is Nothing Then Exit Do
             Loop While rngFound.Address <> firstaddress
        End If
    End With
End Sub

Possibly,

Option Explicit

Sub Reset_Cells()

    Dim c As Range
    
    Application.ScreenUpdating = False
    
    With Sheet1
        On Error Resume Next
        For Each c In .Range("B1:AF500")
            If c.Interior.ColorIndex = 42 Then
                c.Interior.ColorIndex = xlNone
                c.ClearContents
            End If
        Next
    End With

    Application.ScreenUpdating = True
    
End Sub