conditional format to last row

[MARK858]

Hi there, I have a very basic conditional format code that I recorded which works fine as far as it goes, but what I would like to do is apply it down to the last used row rather than a range i.e. last used row rather than row 8185 .

Does anyone know the correct syntax for achieving this as I have never attempted conditional formatting with a macro (and probably still wouldn't if the workbook was just for my use)?

Thanks in advance for any suggestions anyone might have.

Current code below

Range("A2:S8185").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$S2<$T2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

 

[Joe4]

Is there one column that will always have data in it that you can use to determine where the last row is?

Let's say that there is, and it is column "A".

Then we can get the last row like this:

myLastRow=Cells(Rows.Count,"A").End(xlUp).Row

And then we can incorporate that into our range like this:

Range("A2:S" & myLastRow)

 

[MARK858]

Cheers Joe, column A was completely used and all is working well. Thanks a lot for your help.