error message

[lior03]

hello
the following macro turn every cell in a selection that contain a formula
to a red font.
what about an error nessage?.suoopse there are no cells that have a formula
how do i get a msgbox say it?.
thanks

Application.ScreenUpdating = False
On Error Resume Next
selection.CurrentRegion.Select
selection.SpecialCells(xlCellTypeFormulas).Select
selection.Font.ColorIndex = 3
selection.Font.Bold = True
Application.ScreenUpdating = True

[Bob Phillips]

Dim rng As Range
Application.ScreenUpdating = False
On Error Resume Next
Set rng = Selection.CurrentRegion.SpecialCells(xlCellTypeFormulas)
If rng Is Nothing Then
MsgBox "No formulas"
Else
With rng.Font
.ColorIndex = 3
.Bold = True
End With
End If
Application.ScreenUpdating = True

[johnske]

Hi moshe,

Not sure that what you're trying to do will work quite as you want it. For instance, if you click a cell outside the current region and run your code, all the cells inside the current region that have formulas will be red and bold, is that what you want?

The Special cells method is much faster, but is primarily intended to work on the whole worksheet and not just a small region on it, in which case you can omit any reference to the current region. Try this instead (you don't need an error message with this either)
Sub TryThis()
Dim Cell As Range
For Each Cell In Selection.CurrentRegion
If Cell.HasFormula Then
With Cell.Font
.ColorIndex = 3
.Bold = True
End With
End If
Next
End SubRegards,
John