Highlighting Just Last Row of Data

Question

I have been trying to develop a code which highlights the last row of the data but my code highlights the complete last row instead of highlighting A to L.

How to add A to L

Any help will be appreciated.

Dim lngLastRow As Long
 
 lngLastRow = Cells.Find(What:="*", _
 After:=Range("A1"), _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious).Row
 
 Rows(lngLastRow & ":" & lngLastRow).Interior.Color = RGB(255, 217, 102)

Answer 1

Highlight a Row Range

• The first procedure is doing what you asked for in your post.
• If you want to play it safe, rather use the second procedure.
• The third procedure will highlight 'multiple last rows', i.e. the last and several above (asked for in the comments). You cannot run it, you have to call it.
• The fourth procedure is an example of how to use (call) the third procedure. Change the 5 to whatever number of rows you need to highlight.

Option Explicit

Sub HighlightLastRowSimple()
    Dim lRow As Long
    lRow = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    Columns("A:L").Rows(lRow).Interior.Color = RGB(255, 217, 102)
End Sub

Sub HighlightLastRowSafe()
    Dim lCell As Range
    Set lCell = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' empty worksheet
    Columns("A:L").Rows(lCell.Row).Interior.Color = RGB(255, 217, 102)
End Sub

Sub HighlightNLastRows(ByVal n As Long)
    Dim lCell As Range
    Set lCell = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' empty worksheet
    Dim lRow As Long: lRow = lCell.Row
    If lRow < n Then
        MsgBox "You cannot highlight " & n & "  last rows.",
            vbCritical, "Highlight n Last Rows"
    Else
        Columns("A:L").Rows(lRow).Offset(1 - n).Resize(n) _
            .Interior.Color = RGB(255, 217, 102)
    End If
End Sub

Sub Highlight5()
    HighlightNLastRows 5
End Sub

EDIT

• To restrict the operations only to the given columns (A:L) you could rewrite like the following...

Sub HighlightLastRowSimple()
    Const Cols As String = "A:L"
    With Columns(Cols)
        Dim lRow As Long
        lRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        .Rows(lRow).Interior.Color = RGB(255, 217, 102)
    End With
End Sub

Sub HighlightLastRowSafe()
    Const Cols As String = "A:L"
    With Columns(Cols)
        Dim lCell As Range
        Set lCell = .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        .Rows(lCell.Row).Interior.Color = RGB(255, 217, 102)
    End With
End Sub

Sub HighlightNLastRows(ByVal n As Long)
    Const Cols As String = "A:L"
    With Columns(Cols)
        Dim lCell As Range
        Set lCell = .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        Dim lRow As Long: lRow = lCell.Row
        If lRow < n Then
            MsgBox "You cannot highlight " & n & "  last rows.", _
                vbCritical, "Highlight n Last Rows"
        Else
            .Rows(lRow).Offset(1 - n).Resize(n) _
                .Interior.Color = RGB(255, 217, 102)
        End If
    End With
End Sub

... when possible data to the right wouldn't be considered.