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

 

[SkipVought]

hi,

Sub Check_Energy_Codes()
    
    With Cells(11, 16).CurrentRegion
        .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
End Sub

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

 

[coltaalex]

Skip can you please explain me how your code is stopping the conditional formatting ?
Sorry for disturbing you so much ,
what condition did you use to stop the code ?

 

[SkipVought]

What verion of Excel please?

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

 

[coltaalex]

i have 2007

 

[SkipVought]

Right click in the ribbon and select Customize quick acess toolbar (QAT)

Choose commands from All Commands

scroll to Select Current Region and ADD to your QAT.

OK

Now select P11 on your sheet.

Hit the Select Current Region tool icon you just added to your QAT.

Observe what is selected.





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

 

[coltaalex]

ok , it is selecting the whole sheet

 

[SkipVought]

All 1,048,576 rows and 16,384 columns?



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

 

[coltaalex]

yes, right ,

 

[SkipVought]

Do you actually have DATA in every cell on the sheet?

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

 

[coltaalex]

i have data just in the first 2 cells, and in the cell 4, so CF suppose to color the cells 1 and 2, ad stop at 3, because 3 is empty,
now it is coloring also the 4 cells.
Here is my full code, it has tow CF rules, first is checking the duplicates numbers , and second one is coloring in white the empty cells :

Sub Check_Energy_Codes()

Application.Goto Reference:="R11C14:R10000C14"

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Rates,N11)=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
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(N11))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
End With
Selection.FormatConditions(1).StopIfTrue = False

Dim N As Long, i As Long

i = 0
For N = 11 To 10000
If Cells(N, 14) <> "" Then
i = i + (Application.CountIf(Range("Rates"), Cells(N, 14)) = 0)
End If
Next N
If i <> 0 Then
MsgBox "Check Energy Rate Codes"
End If

End Sub

 

[SkipVought]

i have data just in the first 2 cells, and in the cell 4, so CF suppose to color the cells 1 and 2, ad stop at 3, because 3 is empty,

THAT is NOT what your code tells me!!!

Application.Goto Reference:="R11C16:R10000C16"
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

THAT says that your data started in ROW 11 COLUMN 16!!!!

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

 

[coltaalex]

it the same code just the instead of 16 now is 14, because i have to use CF for several columns, but methodology is the same,

 

[SkipVought]

i have data just in the first 2 cells, and in the cell 4, so CF suppose to color the cells 1 and 2, ad stop at 3, because 3 is empty, now it is coloring also the 4 cells.

Please tell me EXACTLY what FORMULA you have in cell 3?

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

 

[coltaalex]

Skip i don't have any formula, in these cells,, i introduce by hand all the codes, the code is combination of some letters and numbers , like: CFT8-100

 

[SkipVought]

Is your concern that the CF is APPLIED to the 3rd cell, or is it that the FORMAT is not displaying as expected?

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

 

[coltaalex]

my concern is that CF is applied to the 3 cell ,
it suppose to stop at the first empty cell

 

[SkipVought]

So it must stop at the first empty cell of WHAT COLUMN, starting in WHAT ROW?

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

 

[coltaalex]

of column 14 Which is N , stating from row 11

 

[SkipVought]

And what columns do you want to have the CF applied to?

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