Fill a blank cell with a color before save, but clear it when data is entered in VBA

36 Views Asked by At

I have code that prevents a user from saving a file if certain cells are empty. The code fills the necessary cells with a jarring red color that forces the user to enter data before attempting to save again. Is there a way to clear the red fill color when text is entered without having to re-save?

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

On Error GoTo Skip
      
    Sheets("Data").Activate
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim HU As String
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Data")

ws.Protect "haha", UserInterfaceOnly:=True

'Customer Number
    If [CustNum] = "" And [Zip1] <> 0 Then
            MsgBox "Please enter Customer Number"
            [CustNum].Interior.ColorIndex = 3
        Else
            [CustNum].Interior.ColorIndex = 2
    End If
{More code}
End Sub
0

There are 0 best solutions below