你應該這麼玩EXCEL-檢查你工作表中的空值或0值

第一種：

代碼如下：

  Dim rng As Range
    Dim R As Range
    Dim TRng As Range
    Set rng = Range("A1:D10")
    For Each R In rng
        If R.Value = "" Or R.Value = 0 Then
            If TRng Is Nothing Then
                Set TRng = R
            Else
                Set TRng = Application.Union(TRng, R)
            End If
        End If
    Next
    MSGBOX TRng.address(0, 0)

第二種：

代碼如下：

  Dim rng As Range
    Dim cell As Range
    Dim address As String
      
    '設置要檢查的區域
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:D10")
      
    '循環檢查每個單元格
    For Each cell In rng
        '檢查單元格是否為空或值為0
        If IsEmpty(cell.Value) Or cell.Value = 0 Then
            '將地址存儲到字元串變數中
            address = cell.address
            '顯示MsgBox並告知地址
            MsgBox "單元格 " & address & " 是空或值為0"
        End If
    Next cell

那如何標註紅色那？

代碼如下：

Dim rng As Range
    Dim cell As Range
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:D10")
    For Each cell In rng
        If cell.Value = 0 Then
            cell.Interior.Color = RGB(255, 0, 0)  ' RGB顏色碼，紅色為(255, 0, 0)
        End If
    Next cell

本期分享結束。