Thanks Dave and Bob,

Dave,

I was about to ask and I attempted myRng.Cells.Replace What:="", Replacement:="",
and it worked fine. Guess I am beginning to slightly get this stuff!

Thanks for the insight to Application.FindFormat. I would not have found that.

EagleOne


Dave Peterson wrote:

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