Inefficient macro won't complete
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Inefficient macro won't complete
 

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" <u18899@uwe wrote in message
news:61dea09cac9c4@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Inefficient macro won't complete
 

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 Phillips wrote:
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

--
Message posted via http://www.officekb.com