CODEWITHSUNDEEP
excelvba
Twaddy
Twaddy

Reputation: 11

VBA code to highlight specific blank cells

I'm hoping someone may be able to help me with some code to highlight empty cells in a specific range, I've found similar topics on the site but I can't get them to work for my scenario, possibly due to my VBA inexperience or they may be the wrong starting point.

I have a questionairre type sheet which users will complete and I want to ensure that once they've been through all the questions that all fields have been completed before they can view their results, the cells which require completing are a little random in terms of location rather than being in a straight forward range, I also have empty rows & columns for aesthetic reasons so I obviously don't want to include them in highlighting them as being blank.

I have code which moves the cursor to the next cell that requires an answer so when completed I'd like the code to run to check for blanks, highlight the border in red and have a message box pop up requested they complete all fields, and then remove the red border when the field was then completed.

This is a section of my cell range I'd like to include E5,E7,H5,H7,L5,L7,J11 so as you can see they're a little random.

Hopefully this all makes sense and one of you wonderful people can help me

Many thanks

Twaddy

Upvotes: 0

Views: 414

Answers (1)

Vasily
Vasily

Reputation: 5782

use this

Sub test()
    Dim oCell As Range, Trigger%
    Trigger = 0
    For Each oCell In [E5,E7,H5,H7,L5,L7,J11]
        If oCell.Value = Empty Then
            With oCell.Borders
                .LineStyle = xlContinuous
                .Color = vbRed
            End With
            Trigger = Trigger + 1
        Else
            oCell.Borders.LineStyle = xlNone
        End If
    Next
    If Trigger = 1 Then
        MsgBox "Mandatory field not filled"
    ElseIf Trigger > 1 Then
        MsgBox "Mandatory fields (" & Trigger & ") not filled"
    End If
End Sub

Upvotes: 1

Related Questions