Excel VBA Conditional Formatting Blank Cells in Row

Question

Right off the bat, I am a total novice at coding, and the extent of my skill is modifying based on Google searches.

I am using Excel 2010, and trying to use VBA to avoid using conditional formatting. The spreadsheet I'm using is a template with 31 columns, 20 of which are mandatory. There is coding in place that if there is an entry in one of these columns for any given row, the other 19 cells must be populated before the file can be saved.

I'm trying to do a similar function to highlight the blank cells preventing the file from being saved. I know that I could use the conditional formatting, but that would require it to look at one cell in each row, as opposed to any of the required cells in that row. I also know that it could do multiple conditional formats, but I know the more layers there are, the slower the spreadsheet will function.

Is this possible? The columns I have that are mandatory are A-D, F-K, M-N, S-Y & AE. In case it would be a factor, I have attached the code I'm using to mandate entry once any of those columns are populated:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim ws As Worksheet
    Dim rg As Range, c As Range
    Dim bCanSave As Boolean
    Dim sWarning As String

    Set ws = Sheets("Main")
    Set rg = ws.Range("A2:A10000,B2:B10000,C2:C10000,D2:D10000,F2:F10000,G2:G10000,H2:H10000,J2:J10000,K2:K10000,N2:N10000,S2:S10000,T2:T10000,U2:U10000,V2:V10000,W2:W10000,X2:X10000,Y2:Y10000,AE2:AE10000")
    sWarning = "File not saved!" & vbNewLine & "Mandatory cells missing in rows: " & vbNewLine

    With ws
        bCanSave = True
        For Each c In rg
            If Not IsEmpty(c) Then
                If .Cells(c.Row, "A") = "" Or .Cells(c.Row, "B") = "" Or .Cells(c.Row, "C") = "" Or .Cells(c.Row, "D") = "" Or .Cells(c.Row, "F") = "" Or .Cells(c.Row, "G") = "" Or .Cells(c.Row, "H") = "" Or .Cells(c.Row, "I") = "" Or .Cells(c.Row, "J") = "" Or .Cells(c.Row, "K") = "" Or .Cells(c.Row, "N") = "" Or .Cells(c.Row, "S") = "" Or .Cells(c.Row, "T") = "" Or .Cells(c.Row, "U") = "" Or .Cells(c.Row, "V") = "" Or .Cells(c.Row, "W") = "" Or .Cells(c.Row, "X") = "" Or .Cells(c.Row, "Y") = "" Or .Cells(c.Row, "AE") = "" Then
                    bCanSave = False
                    sWarning = sWarning & c.Row & ","
                End If
            End If
        Next c
    End With

    If Not bCanSave Then
        MsgBox sWarning, vbExclamation
        Cancel = True
    End If
End Sub

"I am using Excel _____" - is that "____" a placeholder for the year/version? — Mathieu Guindon
Mat's Mug: Yes...edited the OP with Excel 2010. pnuts: Maybe poor wording on my part? If I want to impact conditional formatting on 20 columns, I would need to have 20 dependent conditions. — rant
Just for the cf-part: If you do the conditional formatting for a range like $A$1:$D$100,$F$1:$K$100,$M$1:$N$100,$S$1:$Y$100,$AE$1:$AE$100 and with the setting Format only cells that contain -> specific text & not containing & * you will get this super fast... each cell will only be checked if changed -> no slowdown (at least: nothing a human being is able to notice) :P — Dirk Reichel

Tim Williams Tim Williams · Accepted Answer · 2017-08-28T21:02:22

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Const CHECK_RNG As String = "A1:D1,F1:K1,M1:M1,S1:Y1,AE1"
    Dim ws As Worksheet
    Dim rg As Range
    Dim bCanSave As Boolean, sep As String
    Dim sWarning As String, sMissing As String, r As Long, ct As Long

    Set ws = Sheets("Main")

    sWarning = "File not saved!" & vbNewLine & "Mandatory cells missing in rows: " & vbNewLine
    bCanSave = True

    For r = 2 To 10000
        Set rg = ws.Rows(r).Range(CHECK_RNG)
        rg.Interior.ColorIndex = xlNone '<< clear any previous fill
        ct = Application.CountA(rg)
        If ct > 0 And ct <> rg.Cells.Count Then
            rg.SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow '<< fill empty
            bCanSave = False
            sMissing = sMissing & sep & r
            sep = ","
        End If
    Next r

    If Not bCanSave Then
        MsgBox sWarning & sMissing, vbExclamation
        Cancel = True
    End If
End Sub

Excel VBA Conditional Formatting Blank Cells in Row

1 Answers