VBA is *not* empty

[ireland87]

This code works for empty cells

How can I amend it to work when the cells in column C has a value of "No" for example?

Many thanks

Sub System_Check()
    Dim cell As Range, c As Range, cad As String
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    Set cell = Range("C4:C" & lr).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not cell Is Nothing Then
        For Each c In cell
            cad = cad & c.Offset(, -2) & ", "
        Next
    End If
    If cad <> "" Then
        MsgBox "Systems Closed : " & Left(cad, Len(cad) - 2)
    Else
        MsgBox "all checked"
    End If
End Sub

 

[Yongle]

Amend to suit your own needs

Sub System_Check()
    Dim c As Range, cad As String, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In Range("C4:C" & lr)
        [COLOR=#ff0000]If c = "No"[/COLOR] Then cad = cad & c.Offset(, -2) & ", "
    Next
    If cad <> "" Then
        MsgBox "Systems Closed : " & Left(cad, Len(cad) - 2)
    Else
        MsgBox "all checked"
    End If
End Sub