如何编写VBA循环在Excel列中识别并高亮整数？

完整循环遍历版代码 #

Sub HighlightIntegers()
    Dim lastrow As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("BOM(Costing)")
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 遍历A列所有有效单元格
    For Each cell In ws.Range("A1:A" & lastrow)
        ' 先判断是否为数值，再验证是否为整数
        If IsNumeric(cell.Value) And cell.Value = Int(cell.Value) Then
            ' 设置黄色高亮（可替换为其他颜色，如RGB(255,200,100)）
            cell.Interior.Color = vbYellow
        Else
            ' 非整数清除填充色
            cell.Interior.ColorIndex = xlColorIndexNone
        End If
    Next cell
End Sub

关键逻辑说明 #

• 先绑定目标工作表对象ws，避免跨工作表操作的歧义
• IsNumeric(cell.Value)先过滤非数值内容，防止后续判断报错
• cell.Value = Int(cell.Value)是判断整数的核心：Int函数提取数值的整数部分，若原数值与整数部分相等，则为整数

替代方案（Find批量匹配版） #

如果想用Find方法实现批量查找，可参考以下代码（仅适用于文本格式的整数，数值格式推荐用循环版）：

Sub HighlightIntegersWithFind()
    Dim lastrow As Integer
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim firstFoundAddr As String
    
    Set ws = Worksheets("BOM(Costing)")
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 匹配纯整数的正则模式
    Set foundCell = ws.Range("A1:A" & lastrow).Find( _
        What:="^[0-9]+$", _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchWildcards:=True)
    
    If Not foundCell Is Nothing Then
        firstFoundAddr = foundCell.Address
        Do
            foundCell.Interior.Color = vbYellow
            ' 查找下一个匹配单元格
            Set foundCell = ws.Range("A1:A" & lastrow).FindNext(foundCell)
        Loop While Not foundCell Is Nothing And foundCell.Address <> firstFoundAddr
    End If
End Sub

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