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"
any advice would be very much appreciated.
Thanks in advance
coop123
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"
I have tried to set last column but i am not sure on how to enter it into INDIRECT formula. see below.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
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