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