Loop through worksheets & empty cells based on color

[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

 

[Jim Cone]

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

 

[JE McGimpsey]

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.

 

[Dave Peterson]

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