Delete cell content in entire workbook if background color is yellow

Ankethaton2

New Member
Joined
Feb 23, 2009
Messages
6
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
Try this on a copy of your workbook (untested)

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
End If
Next c
Next ws
End Sub
 

Ankethaton2

New Member
Joined
Feb 23, 2009
Messages
6
Dear pboltonchina

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

Any ideas?
 

Ankethaton2

New Member
Joined
Feb 23, 2009
Messages
6
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

New Member
Joined
Feb 23, 2009
Messages
6
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!
 

Forum statistics

Threads
1,089,284
Messages
5,407,374
Members
403,139
Latest member
MrRadioNumbers

This Week's Hot Topics

Top