Inefficient macro won't complete

[Fox via OfficeKB.com]

I've created a macro that will apply 2 conditional formats to whichever cells
I have selected. The first condition changes the cell text to red if the
cell value = false. The second condition changes the cell text to red if the
cell contains an error (usually N/A). The macro works fine when I test it on
a group of 10 cells or so, but hangs if I try to run it on several thousand
cells. Any ideas on how to improve this?

Sub Highlight_FALSE_and_Errors()
Dim rngCell As Range
Dim rngSelection As Range

Application.ScreenUpdating = False

Set rngSelection = Selection

With rngSelection
.FormatConditions.Delete
For Each rngCell In Selection
With rngCell
.Select
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
Formula1:="FALSE"
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISERROR
(" & ActiveCell.Address & ")"
.FormatConditions(2).Font.ColorIndex = 3
End With
Next rngCell
.Select
End With

Application.ScreenUpdating = True
Set rngCell = Nothing
Set rngSelection = Nothing
End Sub

 

[Bob Phillips]

Sub Highlight_FALSE_and_Errors()

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="FALSE"
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=ISERROR(" & ActiveCell.Address(False, False) & ")"
.FormatConditions(2).Font.ColorIndex = 3
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Fox via OfficeKB.com]

Thanks, that's much better.

I think I tried to write it like that to begin with, but I didn't have (False,
False) for the ActiveCell.Address, therefore the addressing wasn't correct in
the ISERROR formulas.

Sub Highlight_FALSE_and_Errors()

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="FALSE"
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=ISERROR(" & ActiveCell.Address(False, False) & ")"
.FormatConditions(2).Font.ColorIndex = 3
End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I've created a macro that will apply 2 conditional formats to whichever cells
I have selected. The first condition changes the cell text to red if the

[quoted text clipped - 31 lines]

Set rngSelection = Nothing
End Sub