Get last column for indirect formula

[coop123]

Hello

I currrently have this section of a macro which works perfectly, my problem is that the last column changes each time the report is run.
In the case below last column is CO hence indirect formula =INDIRECT(""CO""&ROW())>0"

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(""CO""&ROW())>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

I have tried to set last column but i am not sure on how to enter it into INDIRECT formula. see below.

Dim lc As Long
lc = Cells(3, Columns.Count).End(xlToLeft).Column

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(lc&ROW())>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

any advice would be very much appreciated.

Thanks in advance

coop123

 

[jasonb75]

I see no reason for using INDIRECT here, as with many questions, it is better if you explain what you're trying to do rather than how you're trying to do it.

See if this works as needed.

Sub coop123()
Dim lc As Long, lr As Long
lc = Cells(3, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, lc).End(xlUp).Row

With Range("A3", Cells(lr, lc))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Cells(3, lc).Address(0, 1) & ">0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1)
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        .StopIfTrue = False
    End With
End With
End Sub

 

[coop123]

Hi jasonb75

Thank you your solution works perfectly.

I am highlighting the entire row where the value in the end column of that row has a value greater than zero.

Thanks again

coop123