Loop through worksheets & empty cells based on color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I loop through every cell in every worksheet and if the cell color is
Yellow or Light Yellow reset the cell value depending on its type to "" or 0 ?

I have barely started the code and more questions are coming then answers.

Private Sub cmdClearAllDataCells_Click()
Dim ws As Worksheet
Dim oCell As Range
If MsgBox("Clear ALL quote data?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
For Each oCell In Selection
'
Next
Next
End If
End Sub
 
For each oCell in ws.UsedRange
If oCell.Interior.ColorIndex <> xlColorIndexNone Then

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Eugene_Elder" <[email protected]>
wrote in message
How can I loop through every cell in every worksheet and if the cell color is
Yellow or Light Yellow reset the cell value depending on its type to "" or 0 ?

I have barely started the code and more questions are coming then answers.

Private Sub cmdClearAllDataCells_Click()
Dim ws As Worksheet
Dim oCell As Range
If MsgBox("Clear ALL quote data?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
For Each oCell In Selection
'
Next
Next
End If
End Sub
 
one way:

Public Sub ResetYellow()
Dim ws As Worksheet
Dim rCell As Range
Dim nResult As Long
Dim nColorIndex As Long

nResult = MsgBox(prompt:="Clear ALL quote data?", _
Title:="Clear Data", _
Buttons:=vbYesNo + vbQuestion)
If nResult = vbYes Then
For Each ws In ActiveWorkbook.Worksheets
For Each rCell In ws.UsedRange
With rCell
nColorIndex = .Interior.ColorIndex
If nColorIndex = 6 Or nColorIndex = 36 Then
If IsNumeric(.Value) Then
.Value = 0
Else
.Value = ""
End If
End If
End With
Next rCell
Next ws
End If
End Sub

This assumes your colors are the default palette.
 
If the ranges to clean up don't change, you may want to use Insert|Name|define
and create a couple of range names that you can use:

with worksheets("sheetnamehere")
.range("ResetToZero").value = 0
.range("ResetToEmpty").clearcontents
end with
 
Back
Top