Loop though a range and clear the contents of a cell which has an index color of 33

[Sarah333]

I have a data entry work book and i want to create a macro that loops through a range on F25:AA100 on each tab and clears the contents of cells with an index color of 33 only

 

[RoryA]

Something like:

Sub ClearCellsColour33()
application.screenupdating = false
dim ws as worksheet
for each ws in activeworkbook.worksheets
dim cell as range
for each cell in ws.Range("F25:AA100").cells
   if cell.interior.colorindex = 33 then cell.value = vbnullstring
next cell
next ws
application.screenupdating = true
end sub

 

[Sarah333]

Thank you for responding so quickly, I have modified your code a little as it wasnt running through the sheets, now it loops but does not clear the contents of the cell i have tried several options including writting a zero but nothing

Sub ClearCellsColour33()
'Macro to clear Input cells

'Application.ScreenUpdating = False

Dim ws As Worksheet
Dim a As Integer
Dim rng As Range, cell As Range

Set rng = Range("F25:F28")
a = Application.Worksheets.Count


For i = 1 To a
Worksheets(i).Activate

'For Each ws In ActiveWorkbook.Worksheets

For Each cell In rng
If cell.Interior.ColorIndex = 33 Then cell.ClearContents
Next cell
' Next ws
Next
'Application.ScreenUpdating = True
End Sub

 

[RoryA]

My code above does iterate through the sheets. The first version I posted didn't but I added it in within a minute or two - perhaps you got it before I added that part in?

 

[Sarah333]

yes that now works thank you very much

 

[RoryA]

Glad we could help.