Thread: cell shading
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Mike H. Mike H. is offline
external usenet poster
 
Posts: 471
Default cell shading

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