Macro to clear data based on color fill

[Admin]

Hi

try this.

Sub kTest()
    
    Dim Sht     As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim rngAll  As Range
    Dim r       As Range
    Dim c       As Range
    
    On Error Resume Next
    For Each Sht In Worksheets
        With Sht
            If .UsedRange.Cells.Count > 1 Then
                LastRow = .Cells.Find("*", .Cells(1), , 2, 1, 2).Row
                LastCol = .Cells.Find("*", .Cells(1), , 2, 2, 2).Column
                Set rngAll = .Range(.Cells(5, 1), .Cells(LastRow, LastCol))
                For Each r In rngAll.SpecialCells(2, 23).Areas
                    For Each c In r.Cells
                        If c.Interior.ColorIndex <> -4142 Then
                            c.Clear 'if you want to cleat the both the formats and data
                            'c.ClearContents'removes only data
                        End If
                    Next
                Next
            End If
        End With
    Next
                
End Sub