Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through worksheets & empty cells based on color
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through worksheets & empty cells based on color
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through worksheets & empty cells based on color
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. In article , Eugene_Elder wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through worksheets & empty cells based on color
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 Eugene_Elder wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to loop through all shapes on multiple worksheets and change color | Excel Programming | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
Loop criteria based on cell value and color | Excel Programming | |||
Empty cells in For Loop | Excel Programming | |||
Loop to change cell color based on found value? | Excel Programming |