excel - 仅突出显示最后一行数据
问题描述
我一直在尝试开发一个代码来突出显示数据的最后一行,但我的代码突出显示完整的最后一行而不是突出显示A to L
。
如何添加A to L
任何帮助将不胜感激。
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)
解决方案
突出显示行范围
- 第一个过程是按照您在帖子中的要求进行操作。
- 如果您想安全起见,请使用第二个程序。
- 第三个过程将突出显示“最后多行”,即最后一行和上面的几行(在评论中要求)。你不能运行它,你必须调用它。
- 第四个过程是如何使用(调用)第三个过程的示例。将 5 更改为您需要突出显示的任何行数。
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
编辑
- 要将操作仅限于给定的列 (
A:L
),您可以重写如下...
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
...当可能的数据不会被考虑时。