how to stop at the lastrow 1

[coltaalex]

how to stop at the last row selection "R11C14:R10000C14"
I have a sheet where i have a column with data, number of row every time is different, some time i have less data some times more,
how can i stop my conditional formatting at the last row of data
every time the code is going until 10000, but some time i have just 5000 line, so how can i stop it at 5000
the code bellow is for conditional formatting:



Sub Check_Energy_Codes()

Application.Goto Reference:="R11C16:R10000C16"

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Hours,P11)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[coltaalex]

columns: 14,15, 16, 17, 18, 19, 23, 24, 25,

 

[SkipVought]

Try this...

Sub Check_Energy_Codes()
'columns: 14,15, 16, 17, 18, 19, 23, 24, 25,
    Dim rng As Range, i As Integer
    
    For i = 1 To 2
        Set rng = Cells(11, "N")
        Select Case i
            Case 1
                If rng.Offset(1).Value = "" Then
                    Set rng = Intersect(rng.EntireRow, Range(Columns(14), Columns(19)))
                Else
                    Set rng = Range(Cells(11, "N"), Cells(11, "N").End(xlDown))
                    Set rng = Intersect(rng.EntireRow, Range(Columns(14), Columns(19)))
                End If
            Case 2
                If rng.Offset(1).Value = "" Then
                    Set rng = Intersect(rng.EntireRow, Range(Columns(23), Columns(25)))
                Else
                    Set rng = Range(Cells(11, "N"), Cells(11, "N").End(xlDown))
                    Set rng = Intersect(rng.EntireRow, Range(Columns(23), Columns(25)))
                End If
        End Select
        
        With rng
            .FormatConditions.Add _
                Type:=xlExpression, _
                Formula1:="=COUNTIF(Hours,P11)=0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[coltaalex]

now i have to analyze
and the bottom i can add my message box code ?
and i see you did just the fist CF rule ( count if)
the same thing i have to do for the second rule ( TRIM("N11")=0)

 

[coltaalex]

Thank you Skip for all your help and support

Have a great evening,