Macro to clear data based on color fill

[Howardc]

I would like code to clear data on all sheets from row 5 onwards where the data has been colored using the color fill, except formulas-see sample data attached

Your assistance is most appreciated.

I have also posted on Mr Excel.com-see link below


Macro to clear data

[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

[Howardc]

Thanks for the help, much appreciated

[Howardc]

Hi admin

I would like to amend the code so that sheet 'main" is ignored when the colored data are cleared

Your assistance is most appreciated

[Admin]

Hi

Try to modify yourself and learn VBA !

here is the way to avoid sheet Main.

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
        If LCase$(Sht.Name) <> "main" Then
            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
        End If
    Next
                
End Sub

[Howardc]

Hi Admin

Thanks for the help. Are there any good tutorials or books on VBA that you can recommend to novices?

[Admin]

Thanks for the feedback.

Have a look at http://www.excelfox.com/forum/f13/le...ful-links-144/

[Howardc]

Thanks for the links. i'm sure that I will find these useful