VBA to select formula cells OK -but want certain cells with ColorI

[Guest]

Using XL 2003 & 97

The following subroutine works.

That said, how can I fine formula cells with a "!" in the cell. (In short,
probably a cell formula referring to another sheet)

With the following code I would like all formula cells set to colorindex 6
(yellow)
but all formula cells with "!" set to a color index of 3 (red)

I tried to use an If statement but to no avail.

Sub SelectFormulaColor()
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.DisplayFormulas = True
ActiveWindow.Zoom = 75
End Sub

TIA Dennis

 

[Gord Dibben]

Dennis

Sub Find_Cells()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "*!*" Then
cel.Interior.ColorIndex = 6
Else: cel.Interior.ColorIndex = 3
End If
End If
Next
End Sub

Gord Dibben Excel MVP

 

[Guest]

Thank you for your time and knowledge! It works fine.