循环遍历单元格区域时，如何获取另一列对应单元格并高亮

现有Excel数据，需在A列Id值小于200时，高亮D列对应行的单元格。以下为初始代码，补充高亮实现的最简方法有两种：

初始代码 #

Dim LoopingRange As Range
Dim CheckRange As Range

Dim LastCellRow As Long, SecondToLastCellRow As Long

With ThisWorkbook.Sheets("Sheet1")

    LastCellRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    SecondToLastCellRow = LastCellRow - 1
    
    Set LoopingRange = .Range("A2:A" & SecondToLastCellRow)
    Set CheckRange = .Range("D2:D" & SecondToLastCellRow)
    
End With

Dim Cell As Range
For Each Cell In LoopingRange

    If Cell.Value2 < 200 Then
        
        'Highlight the corresponding cell in column D
                
    End If
            
Next Cell

最简实现方法 #

方法1：通过行号定位D列单元格 #

利用循环中Cell的Row属性获取当前行号，直接定位到同一行的D列单元格，设置填充颜色：

If Cell.Value2 < 200 Then
    ' 用ColorIndex设置高亮（6代表黄色，可根据需求修改）
    ThisWorkbook.Sheets("Sheet1").Cells(Cell.Row, "D").Interior.ColorIndex = 6
End If

方法2：用Offset偏移列 #

A列到D列横向偏移3列（A是第1列，D是第4列），通过Offset(0, 3)直接获取同一行的D列单元格：

If Cell.Value2 < 200 Then
    ' 高亮为黄色，也可使用RGB自定义颜色：.Interior.Color = RGB(255, 255, 0)
    Cell.Offset(0, 3).Interior.ColorIndex = 6
End If

完整代码示例（以Offset方法为例） #

Dim LoopingRange As Range
Dim CheckRange As Range

Dim LastCellRow As Long, SecondToLastCellRow As Long

With ThisWorkbook.Sheets("Sheet1")

    LastCellRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    SecondToLastCellRow = LastCellRow - 1
    
    Set LoopingRange = .Range("A2:A" & SecondToLastCellRow)
    Set CheckRange = .Range("D2:D" & SecondToLastCellRow)
    
End With

Dim Cell As Range
For Each Cell In LoopingRange

    If Cell.Value2 < 200 Then
        ' 高亮对应D列单元格为黄色
        Cell.Offset(0, 3).Interior.ColorIndex = 6
    End If
            
Next Cell

内容的提问来源于stack exchange，提问作者DecimalTurn