How to select Formula cells with red interior?
Oops. I forgot to limit the replace to just the range with the formulas!
Option Explicit
Sub testme()
Dim myRng As Range
With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
End With
If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If
With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With
With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With
myRng.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True
End Sub
Dave Peterson wrote:
With xl2002+, excel added a way to replace the formatting of cells, too. You
can see it under the Options button on the edit|Replace dialog.
In code:
Option Explicit
Sub testme()
Dim myRng As Range
With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If
With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With
With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With
.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True
End With
End Sub
In my workbook, red had a colorindex of 3 and bright yellow is 27. These may
differ for you. You can record a macro while changing the fill color to see
what your workbook uses. (Colors travel with the workbook, so it could be
different for each workbook--if someone customized the color scheme.)
wrote:
2003
Is there a way to select all formula cells with i.e. a red interior color in one selection?
I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?
Thanks
EagleOne
--
Dave Peterson
--
Dave Peterson
|