Conditional formatting with xlR1C1 formula

Question

Trying to apply conditional formatting via VBA to a spreadsheet that will have 25K+ rows. There isnt a set lastcolumn or last row so having difficulty applying the below code for some reason. When I check the condition formatting on each row it keeps referring to row 3 all the time. If I put RC" & lastCol +3 &"=FALSE" it recognises this as cell RC25 for example:

Range(Cells(3, FoundCol), Cells(lastrowRecon, FoundCol)).Select
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R[]C" & lastCol + 3 & "=FALSE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With

Unknown Unknown · Accepted Answer · 2017-04-11T20:47:18

_{When I check the condition formatting on each row it keeps referring to row 3 all the time ... If I put RC" & lastCol +3 &"=FALSE" it recognises this as cell RC25 for example}

RC25 is an xlA1 style cell reference. It is the 25th row in column RC .

You cannot put an xlR1C1 formula into a Conditional Formatting Rule when the Application.ReferenceStyle is xlA1; conversely, you cannot put an xlA1 style formula into a system currently running an xlR1C1 formula style. However, it is easy enough to flip between the two or use Application.ConvertFormula to switch the formula for you. There is no Formula1R1C1 parameter to the .FormatConditions.Add method.

I think your xlR1C1 formula would be better as "=NOT(RC" & (lastCol + 3) & ")" .

Sub wqewqwew()
    Dim lastCol As Long, xlA1formula As String
    lastCol = 22
    With Selection
        .FormatConditions.Delete
        Application.ReferenceStyle = xlA1
        'when Application.ReferenceStyle = xlA1
        xlA1formula = Application.ConvertFormula("=NOT(RC" & (lastCol + 3) & ")", xlR1C1, xlA1, , .Cells(1))
        With .FormatConditions.Add(Type:=xlExpression, Formula1:=xlA1formula)
            .Interior.Color = 255
            .SetFirstPriority
        End With

        .FormatConditions.Delete
        Application.ReferenceStyle = xlR1C1
        'when Application.ReferenceStyle = xlR1C1
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=NOT(RC" & (lastCol + 3) & ")")
            .Interior.Color = 255
            .SetFirstPriority
        End With

        'switch back
        Application.ReferenceStyle = xlA1
    End With
End Sub

Conditional formatting with xlR1C1 formula

2 Answers