VBA Conditional Formatting

[jjlafond]

Hello,

Is it possible to apply conditional formatting & borders to cells without highlighting / selecting them?
ie. Can I apply conditional formatting (CF) & borders by using .Activate or something similar (instead of .Select)?

I am applying several different CFs to a small range right now by selecting them (using vba). But it is very slow, taking 5-10 seconds to complete, and I can actually watch every step happening.

I will be using the same CFs on a much larger range soon, so I want to speed it up considerably (otherwise i'll have to take a lucn break before it finishes running ).





An example of my current conditional formatting:ActiveCell.Resize(6, 1).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=$M$6", Formula2:="=$L$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

An example of my current formatting:
ActiveCell.Resize(6, NumCav).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)..... 'more border conditions follow.

There is a lot more of this going on too....

 

[VoG]

Try like this

With ActiveCell.Resize(6, 1)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=$M$6", Formula2:="=$L$6"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

 

[jjlafond]

Thank you, this does speed it up some.

It is still somewhat slow, but now the program does not have to take the time to highlight everything then apply formatting, it skips over the visible highlight.