VBA so it wont save if shaded cells are empty

[Godismyking]

Hey Folks I am running the following code so all shaded cells have to be filled out otherwise it won't let me save the excel file and so far I have this but seems not to work as it only works on some cells and only if some of that particular cell are selected and it is because of the For condition see below in bold. How do I condition the "For" so It will look only for the shaded boxes? So what I want is I have a form that needs to be filled out. The user has to fill out ALL shaded boxes if any of the shaded boxes are left incomplete it will not save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Step 1: Check to see if Cells are blank


Dim Var1 As String
Dim Var2 As String
Dim rCell As Range
Dim lColor As Long
lColor = RGB(242, 242, 242)
Var1 = Range("B7")
Var2 = Range("B8")


   ' This is the part I am stuck on as I do not know how to code for it to identify all shaded cells only
   
For Each rCell In Selection       
If rCell.Interior.Color = lColor Then
If rCell.Value = "" Then


      
'Step 2: Blank: cancel the Close and tell the user
        Cancel = True
     
        
    MsgBox (Var1 + " " & "please complete all shaded boxes so CSS can quickly process your order for" + " " & Var2 + "")
        'Step 3: Not Blank; Save and Close
        
            Else
        SaveChanges = True
     
      
    End If
    
    End If
    
    Next


    
    
    
   
    
    
End Sub

 

[PhilS2520]

If you know the address of the shaded cells, why don’t you test them one by one?

if the user needs to interact with the form, why not force the user to enter the data in the form. Much easier!

 

[Godismyking]

Thanks but not helpful. Anybody else?

 

[JoeMo]

What sheet or sheets contain the shaded cells?

 

[JoeMo]

Does this work? Change the sheet name (in red font) to suit.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Step 1: Check to see if Cells are blank
Dim Var1 As String
Dim Var2 As String
Dim rCell As Range
Dim lColor As Long
lColor = RGB(242, 242, 242)
Var1 = Range("B7")
Var2 = Range("B8")
For Each rCell In Sheets("Sheet6").UsedRange  'Change Sheet name to suit
    If rCell.Interior.Color = lColor And rCell.Value = "" Then
        Cancel = True
        MsgBox (Var1 + " " & "please complete all shaded boxes so CSS can quickly process your order for" + " " & Var2 + "")
        Exit Sub
    End If
Next rCell
        'Step 3: Not Blank; Save and Close
        SaveChanges = True
End Sub