Hi,


Validate against an array. Note at the beginning of your code I've created
an array of values to validate against. Extend this to your full list. In the
midddle of the code there's an If loop that validates against each element of
the array

Sub Validate_Status()
'checks for errors in status names entries
Dim MyArray()
Dim There As Boolean
Dim ErrCount
Dim c As Range
MyArray = Array("AL", "SL", "BT", "PP")
ThisWorkbook.Sheets("Sheet1").Select
Range("A1:A10").ClearFormats

For Each c In Range("A1:A10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
'validate the staus codes

There = False
For i = 0 To UBound(MyArray)
If c.Value = MyArray(i) Then
There = True
Exit For
End If
Next
c.Interior.ColorIndex = 3
If There = False Then ErrCount = ErrCount + 1

End If
End If
Next c
MsgBox ErrCount
End Sub


Mike


"MJKelly" wrote:


Hi,
The code below checks a range for errors. I want to build on the line
which checks the value for "AL" or "SL" to check for some 30 different
possibilities. Is there a better way of doing this than Xor c.value =
"Pm" Xor c.value = "XD" etc. Like I say this will be around 30
different possibilities.

I tried
if c.value < ("AL", "SL", "PM") then
do something
end if

but this doesnt work. Is there a syntax I can use other than a long
winded Xor?

Sub Validate_Status()
'checks for errors in status names entries

Dim ErrCount
Dim c As Range

ThisWorkbook.Sheets("Sheet1").Select
Range("A1:A10").ClearFormats

For Each c In Range("A1:A10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
'validate the staus codes
If Not c.Value = "AL" Xor c.Value = "SL" Then
c.Interior.ColorIndex = 3
ErrCount = ErrCount + 1
End If
End If
End If
Next c

MsgBox ErrCount

End Sub

Kind regards,
Matt