Zero values being read-in as missing values in VBA

I am trying to trace through a sheet and highlight cells that come back as blank values or 0 values (or "NaN") values. I wrote a nested for loop to do this for me, however, I am seeing a weird item.

The cell in question should change to red if the value equals 0. When I run my macro, however, the end result is that all the cells that pass the if statement conditions end as yellow, even if 0. Perhaps VBA reads blank cells as 0, or the cells are not truly blank? Have I made a mistake, or am I incorrect on the logic of VBA?

    'i is for the columns
For i = 2 To 4 'lastColumn

    totalCounter = 0
    outageCounter = 0
    missingCounter = 0

    'j is for the rows
    For j = 6 To lastRow

        'highlight if production outage
        If mainSheet.Cells(j, i).Value = 0 Then
            mainSheet.Cells(j, i).Interior.Color = vbRed
            outageCounter = outageCounter + 1
        End If

        'highlight if comm outage
        If mainSheet.Cells(j, i).Value = "" Or mainSheet.Cells(j, i).Value = "NaN" Then
            mainSheet.Cells(j, i).Interior.Color = vbYellow
            missingCounter = missingCounter + 1
        End If

        totalCounter = totalCounter + 1

    Next j

    mainSheet.Cells(lastRow + 2, i).Value = missingCounter
    mainSheet.Cells(lastRow + 3, i).Value = outageCounter
    mainSheet.Cells(lastRow + 4, i).Value = totalCounter

Best Answer

In Excel the default value of an empty cell is zero, so you need to check there's actually some content before checking for zero:

 Dim c As Range

 For j = 6 To lastRow

    Set c = mainSheet.Cells(j, i)

    'highlight if production outage
    If Len(c.Value) > 0 And c.Value = 0 Then
        c.Interior.Color = vbRed
        outageCounter = outageCounter + 1
    End If

    'highlight if comm outage
    If c.Value = "" Or c.Value = "NaN" Then
        c.Interior.Color = vbYellow
        missingCounter = missingCounter + 1
    End If

    totalCounter = totalCounter + 1

Next j

Answer 2

you can use Text property and check it against 0 ("production outage") and "" or "NaN" ("comm outage")

you can also use some With - End With to reduce typing and memory accesses

With mainSheet
    For i = 2 To 4 'lastColumn

        totalCounter = 0
        outageCounter = 0
        missingCounter = 0

        'j is for the rows
        For j = 6 To lastrow

            With .Cells(j, i)
                Select Case .Text
                    Case 0
                        'highlight if production outage
                        .Interior.Color = vbRed
                        outageCounter = outageCounter + 1

                    Case "", "NaN"
                        'highlight if comm outage
                        .Interior.Color = vbYellow
                        missingCounter = missingCounter + 1
                End Select

                totalCounter = totalCounter + 1
            End With
        Next j

        .Cells(lastrow + 2, i).value = missingCounter
        .Cells(lastrow + 3, i).value = outageCounter
        .Cells(lastrow + 4, i).value = totalCounter
    Next i
End With