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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,903
Messages
5,411,132
Members
403,343
Latest member
Vinay M

This Week's Hot Topics

Top