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 (http://www.mrexcel.com/forum/excel-questions/671505-macro-clear-data.html)

[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/learn-excel-vba-useful-links-144/

[Howardc]

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