Macro to clear data based on color fill

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

Hi

try this.

Code:

---

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

---

Thanks for the help, much appreciated

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

Hi

Try to modify yourself and learn VBA !

here is the way to avoid sheet Main.

Code:

---

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

---

Hi Admin

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

Thanks for the feedback.

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

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