how to stop at the last row

[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).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

[p45cal]

smallest change but not very elegant:
Application.Goto Reference:="R11C16:R" & Cells(Rows.Count, "P").End(xlUp).Row & "C16"
which assumes that there's always something in column P in the bottom row. If not, then we'll need to tweak.

[Bob Phillips]

Sub Check_Energy_Codes()
Dim LastRow As Long

LastRow = Cells(Rows.Count, 16).End(xlip).Row

With Range(Cells(11, 16), Cells(LastRow, 6))

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTIF(Hours,P11)=0"
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With .FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
End Sub

[coltaalex]

Thank you p45cal,
it works but , if i skip one row, and insert in the next cell something, it detect that cell also, i want the code to stop at the first empty cell
thank you

[p45cal]

Application.Goto Reference:="R11C16:R" & Cells(11, "P").End(xlDown).Row & "C16"

[coltaalex]

Thank you XLD,
it give me Debug at this row :
LastRow = Cells(Rows.Count, 16).End(xlip).Row

[Bob Phillips]

Thank you XLD,
it give me Debug at this row :
LastRow = Cells(Rows.Count, 16).End(xlip).Row
That should ber

LastRow = Cells(Rows.Count, 16).End(xlUp).Row

[coltaalex]

thank you very much p45cal it works, !!!

[coltaalex]

how about this row XLD :


.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty

[coltaalex]

Sorry p45Cal
why it working good perfectly , but if i live the second row empty it not working, its starting coloring the rest of the cell also, ( if in case by mistake i introduce some values in cell 3,
when i fill the cells 1, 2, and skip 3 , and fill 4 , it work perfectly, its coloring the cells 1, 2 and stops
what is a deal with cell 2 ?

[Bob Phillips]

how about this row XLD :


.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty

That's just the forumn inserting an extraneous space

[coltaalex]

still is showing at that row debug error '9'
sorry for disturbing you, and thank you very much for all your support and help,

[p45cal]

Sorry p45Cal
why it working good perfectly , but if i live the second row empty it not working, its starting coloring the rest of the cell also, ( if in case by mistake i introduce some values in cell 3,
when i fill the cells 1, 2, and skip 3 , and fill 4 , it work perfectly, its coloring the cells 1, 2 and stops
what is a deal with cell 2 ?
Well, you did say:
i want the code to stop at the first empty cellThe code does the same as when you press End on the keyboard and then the down arrow key. If this column is not reliable, is there another column in the table that will always have a value in as far as the table goes down? Can you tell me if row 10 has headers in? Maybe we can use current region; experiment by selecting various cells then pressing F5 and clicking on the Special… button and choosing Current Region, then click OK until, regardless of what is/is not in the table the correct number of rows is chosen (it doesn't matter if the wrong number of columns is chosen). Then tell me what that cell/those cells are and I'll code it for you.

[Bob Phillips]

This seems to work (although I am not sure of the actual condition)



Sub Check_Energy_Codes()
Dim LastRow As Long

LastRow = Cells(Rows.Count, 16).End(xlUp).Row

With Range(Cells(11, 16), Cells(LastRow, 6))

.Select
.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