VBA to check for Blank Cells in various ranges, and highlight those cells

[grabrail]

Hi

I am working on a form, which it is crucial is filled in completely by engineers.

I have a lot of different ranges of cells that need to be checked.

Basically What I am doing is adding a "validate form" button which will a) spell check a specific set of cells. (I have done this no problem) b) check the form to ensure all required cells have been completed. (Some of the cells have a preselected value e.g. "Select" that needs to be changed to a value from a drop down list, some of the cells are just blank but need to be populated with text, and some are blank but have a drop down list.)

What I want to happen is, when the validate form is pressed, all required cells are checked for either being blank, or not changed from the default option, and a msg pops up Saying something like "Please complete form" when they click ok, I want the cells that need to be completed to be highlighted in some way.

Is this possible?

 

[grabrail]

For anybody interseted I managed to get this done with the following code:

Private Sub validate_Click()
    
    
    
    Dim Data As Range
    Dim cell As Range
    Dim Counter As Integer
    
    Counter = 0
    
    'Check Report Header
    
    Set currentsheet = ActiveWorkbook.Sheets("VI Sheet")
    Set Data = currentsheet.Range("D2:D7,I2:I6")

    For Each cell In Data
    If cell.Value = "" Then
        cell.Interior.ColorIndex = 6
        Counter = Counter + 1
    Else
    cell.Interior.ColorIndex = 2
    End If
    
    Next
    
    'Check Check List
    
    Set Data = currentsheet.Range("E13:E27, E29:E39, J13:J35,J37:J39")
    
     For Each cell In Data
    If cell.Value = "" Or cell.Value = "-SELECT-" Then
        cell.Interior.ColorIndex = 6
        Counter = Counter + 1
    Else
    cell.Interior.ColorIndex = 2
    End If
    
    Next
    
    
    'Check Page 2 Header
    
    Set Data = currentsheet.Range("D42:D47,I42:I46")

    For Each cell In Data
    If cell.Value = "" Then
        cell.Interior.ColorIndex = 6
        Counter = Counter + 1
    Else
    cell.Interior.ColorIndex = 2
    End If

    Next
    
    'Check Wheel 7 Tyre Information
    
    Set Data = currentsheet.Range("C95:C100, E95:e100, h95:h96, h98, h100, i95:j100, C103:C106,  E103:E106, H104, H106, I103:J106, C108:C111, E108:E111,H108, H110,I108:J111")

    For Each cell In Data
    If cell.Value = "" Then
        cell.Interior.ColorIndex = 6
        Counter = Counter + 1
    Else
    cell.Interior.ColorIndex = 2
    End If

    Next
    
     'Check Headlights and below
    
    Set Data = currentsheet.Range("E114, G114, E116:E119, G116, E121, E123, E125, E127:E129, G123, E133:E134, E136:E139 ")

    For Each cell In Data
    If cell.Value = "" Then
        cell.Interior.ColorIndex = 6
        Counter = Counter + 1
    Else
    cell.Interior.ColorIndex = 2
    End If

    Next
    
    
    If Counter > 0 Then
        
        MsgBox ("Please complete boxes highlighted in yellow, Then Validate Form Again")
        
        Exit Sub
    Else
    MsgBox ("No Issues Found, OK to Save Form")
    End If
    
    'Spell Check
    Range("C52:C80").CheckSpelling
    
    
    
    CheckBox1.Value = True

End Sub