VBA .FormatConditions in With Statement

[AlexB123]

Hi all,

I am rewriting VBA in an excel macro workbook that was originally created with the VBA Recorder. I tried to clean it up, but was unsure if I could get rid of every reference to the "Selection" of a range of values.

Is my syntax OK? Do I need to change parts of it? What can I do to get this to work?

FYI - mainSh12 is a reference to a Worksheet Object.

Set Rng24 = mainSh12.Range("P2:P" & lastrowSEres)
        
        With Rng24
            .Select
            .FormatConditions.Delete 'Either this line, or the 4th is Unnecc.
            .FormatConditions.Add Type:=xlExpression, Formula1:="=P2=""Research"""
            .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 65535
            .FormatConditions(1).StopIfTrue = False
        End With

 

[Fluff]

How about

        With rng24
            .FormatConditions.delete 'Either this line, or the 4th is Unnecc.
            .FormatConditions.Add Type:=xlExpression, Formula1:="=P2=""Research"""
            .FormatConditions(rng24.FormatConditions.count).SetFirstPriority
            .FormatConditions(1).Interior.Color = 65535
            .FormatConditions(1).StopIfTrue = False
        End With

 

[AlexB123]

I removed two of the conditions, but rng24 worked!

Thanks!

 

[Fazza]

Suggest you check it gives the result you want - the correct conditional format on the range.

In my experience, with conditional formatting via VBA taking out the range.select step will result in the formulas referencing the wrong cells,
UNLESS you use R1C1 notation.

So when applying conditional format (in VBA) and there are formulas referring to range addresses use R1C1 notation (then you're OK without the .Select)

cheers