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