Delete cell content in entire workbook if background color is yellow

[Ankethaton2]

I am looking for a vba code that would delete the content of a cell based on its background color.

My workbook has several worksheets in which cells have a light yellow background if the user can input in them.

Now I created a button which would reset the entire model, ie delete the content that the user placed in the yellow cells.

My code is as follows, but does not seem to work:

Sub reset()
Dim c As Range
For Each c In ThisWorkbook
If c.Interior.ColorIndex = 36 Then c.ClearContents
Next c
End Sub

Thanks for any help on this.

 

[pboltonchina]

Try this on a copy of your workbook (untested)

Sub reset()
Dim c As Range
For Each ws In Sheets
ws.Activate
For Each c in ActiveSheet.UsedRange
If c.Interior.ColorIndex = 36 Then c.ClearContents
End If
Next c
Next ws
End Sub

 

[Ankethaton2]

Dear pboltonchina

Thank you for this. Unfortunately, I get the message "End If without Block If"

Any ideas?

 

[pboltonchina]

Try deleting the 'End If' and then run it.

 

[Ankethaton2]

Thanks. The error message disappears, but there still seems to be something not working. The text "c.Clearcontents" is highlighted in yellow in the debugger. Any ideas? Thanks for your help so far! Below what I have now as code:

Sub reset()
Dim c As Range
For Each ws In Sheets
ws.Activate
For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 36 Then c.ClearContents
Next c
Next ws
End Sub

 

[Ankethaton2]

Actually, the error message is "runtime error 1004, cannot change part of a merged cell".

I have indeed cells that are merged and have content. could this prevent the "c.clearcontents" command?

Would there be a way around this?

Thanks!