cell shading

[dunny_budgie]

Is there a way to automatically reset the value of all cells in a spreasheet
that have been shaded a specific colour?

In my spreadsheet I have (for ease of use) shaded yellow all the cells that
the user can input data into. Anything not shaded yellow is protected and
unavailable to the user. What i want to do is clear all the user inputs at
the end of each month, so that at the start of the following month they can
re-use the spreadsheet.

Can this be done? i could not find anything in VBA, but there must be
something.

Any help is appreciated!

 

[Gary''s Student]

Sub clearum()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next
End Sub

The 6 is for yellow. Adapt as you like.

 

[Dave Peterson]

In xl2002+, you can use edit|replace to clear the cells that have a common
formatting.

If you're using an earlier version of excel, then I would use:

Select the cells that need to be cleared
Use Insert|Name|Insert (xl2003 menus) to create a name for this selected range.

Then whenever I wanted to clear that range, I could select the range (ctrl-g and
type the name) and then hit the delete key (on the keyboard).

 

[Tom Hutchins]

Try this macro

Sub ClearColoredCells()
Dim c As Range, ClrVal
Application.InputBox("Select one of the cells to be cleared", Type:=8).Select
ClrVal = Selection.Interior.ColorIndex
ActiveSheet.UsedRange.Select
For Each c In Selection
If c.Interior.ColorIndex = ClrVal Then
c.ClearContents
End If
Next c
End Sub

It pauses & asks you to select any one of the shaded cells to be cleared.
Then it clears all the cells with that same interior fill color.

Hope this helps,

Hutch

 

[Mike H.]

There are a couple ways:

1. Just save a copy of the file, empty and then replace the sheet that gets
the input each month.
2. Create a macro that does something like this:

Note: if the cell color is not the "6" listed below, that # would need to be
adjusted.
to test it, record a macro and adjust the same color that is in a yellow
cell. Then see what value it used.

Sub ClearYellowCells()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastCol As Long

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
Let LastRow = ActiveCell.Row
Let LastCol = ActiveCell.Column
For X = 1 To LastRow
For Y = 1 To LastCol
If Cells(X, Y).Interior.ColorIndex = 6 Then
Cells(X, Y).Value = Empty
End If
Next
Next
MsgBox ("done resetting all yellow cells!")

End Sub