Highlighting cells when errors occur

[cdb]

I have the code below in a spreadsheet that is filled in by various people,
and what it is supposed to do is prevent saving if any of the mandatory
fields aren't filled in.

This all works fine, but now I want to change it so it highlights the
individual cells that need filling in, but can't get my head around it. Can
anyone offer any help??

Cheers,

cdb

Code:

Sub ValidationCheck()

Dim myError As String, myMsg As String, Counter As Integer, EndCount As
Integer, myError2 As String

Counter = 3

Range("A3").Select
'Selection.End(xlDown).Select
EndCount = 53

myError = ""
myMsg = ""

While Counter < EndCount

myError2 = myError

If (Range("A" & Counter) = "" And Range("B" & Counter) = "" And
Range("C" & Counter) = "" And Range("D" & Counter) = "" And Range("E" &
Counter) = "" And Range("F" & Counter) = "" And Range("H" & Counter) = "" And
Range("I" & Counter) = "" And Range("J" & Counter) = "" And Range("N" &
Counter) = "" And Range("O" & Counter) = "" And Range("P" & Counter) = "" And
Range("Q" & Counter) = "" And Range("R" & Counter) = "" And Range("S" &
Counter) = "" And Range("W" & Counter) = "" And Range("X" & Counter) = "" And
Range("Z" & Counter) = "" And Range("AA" & Counter) = "" And Range("AB" &
Counter) = "" And Range("AC" & Counter) = "" And Range("AL" & Counter) = ""
And Range("AN" & Counter) = "" And Range("AO" & Counter) = "" And Range("AQ"
& Counter) = "" And Range("AR" & Counter) = "" And Range("AS" & Counter) = ""
And Range("AT" & Counter) = "") = True Then GoTo EndBit
If (Range("A" & Counter) <> "" And Range("B" & Counter) <> "" And
Range("C" & Counter) <> "" And Range("D" & Counter) <> "" And Range("E" &
Counter) <> "" And Range("F" & Counter) <> "" And Range("H" & Counter) <> ""
And Range("I" & Counter) <> "" And Range("J" & Counter) <> "" And Range("N" &
Counter) <> "" And Range("O" & Counter) <> "" And Range("P" & Counter) <> ""
And Range("Q" & Counter) <> "" And Range("R" & Counter) <> "" And Range("S" &
Counter) <> "" And Range("W" & Counter) <> "" And Range("X" & Counter) <> ""
And Range("Z" & Counter) <> "" And Range("AA" & Counter) <> "" And Range("AB"
& Counter) <> "" And Range("AC" & Counter) <> "" And Range("AL" & Counter) <>
"" And Range("AN" & Counter) <> "" And Range("AO" & Counter) <> "" And
Range("AQ" & Counter) <> "" And Range("AR" & Counter) <> "" And Range("AS" &
Counter) <> "" And Range("AT" & Counter) <> "") = False Then myError =
myError & "- Not all the mandatory fields have been filled in on Line " &
Counter & vbCrLf
If Range("H" & Counter) = "Mrs" And Range("L" & Counter) = "" Then
myError = myError & "- Previous Surname missing on Line " & Counter & vbCrLf
If Range("AC" & Counter) = "Yes" And Range("AD" & Counter) = "" Then
myError = myError & "- Resident From Date missing on Line " & Counter & vbCrLf
If Range("AC" & Counter) = "Yes" And Range("AE" & Counter) = "" Then
myError = myError & "- Previous Address 1 missing on Line " & Counter & vbCrLf
If Range("AF" & Counter) = "Yes" And Range("AG" & Counter) = "" Then
myError = myError & "- Resident From Date missing on Line " & Counter & vbCrLf
If Range("AF" & Counter) = "Yes" And Range("AH" & Counter) = "" Then
myError = myError & "- Previous Address 2 missing on Line " & Counter & vbCrLf
If Range("AI" & Counter) = "Yes" And Range("AJ" & Counter) = "" Then
myError = myError & "- Resident From Date missing on Line " & Counter & vbCrLf
If Range("AI" & Counter) = "Yes" And Range("AK" & Counter) = "" Then
myError = myError & "- Previous Address 3 missing on Line " & Counter & vbCrLf

If myError2 <> myError Then Range("A" & Counter & ":AT" &
Counter).Interior.ColorIndex = 6

Counter = Counter + 1
Wend

EndBit:
If myError <> "" Then
myMsg = MsgBox("The following errors have occured while trying to
save this document:" & vbCrLf & vbCrLf & myError, vbCritical + vbOKOnly,
"Recruitment Campaign Request")
If myMsg = vbOK Then Cancel = True
End If


End Sub

 

[merjet]

In module for sheet with required input:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Range("A1:A3")
If IsEmpty(c) Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 0
End If
Next c
End Sub

In ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim c As Range
For Each c In Range("A1:A3")
If IsEmpty(c) Then Cancel = True
Next c
If Cancel = True Then MsgBox "File can't be saved if blah-blah cells
are empty."
End Sub

Change cell references to suit.

Hth,
Merjet

 

[cdb]

Cheers for this. It gives me a place to start from. The problem I'm going to
have is that not every cell is mandatory, so how do I do this based on a
wider selection?

eg
Columns A, B, C, F, G, I, K, L and T are mandatory

There are also some other columns that are mandatory based on the outputs of
others

eg
Column M is mandatory is Column L = "Yes"

How do I build this in? I also need the error message that was in my
original code to be kept to, so can I do both of these in the same code?

Cheers,

cdb

 

[merjet]

Column M is mandatory is Column L = "Yes"

How do I build this in? I also need the error message that was in my
original code to be kept to, so can I do both of these in the same code?

I don't imagine you mean entire columns, so I will assume only one
row. Add the following.

If Range("L1") = "Yes" And IsEmpty(Range("M1")) Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 0
End If

If Range("L1") = "Yes" And IsEmpty(Range("M1")) _
Then Cancel = True

Simply substitute your MsgBox text for mine.

Merjet