Inefficient macro won't complete

  • Thread starter Fox via OfficeKB.com
  • Start date
F

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
 
B

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)
 
F

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.

Bob said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top