I need to check data in a range of 100 cells. The value can be blank or
single character only from these upper case letters (A, B, C, N, P, R, S, T,
X, Y, Z). If the data is not correct prompt user to fix it. Is there better
way to validate the cells that multiple if ..then .. else?

DataValidation
Choose List
enter A,B,C,N,P,R,S,T,X,Y,Z (without any quotes) in Source Box
Click OK

Et voila!

HTH
--
AP

"
a écrit dans le message
de ...
I need to check data in a range of 100 cells. The value can be blank or
single character only from these upper case letters (A, B, C, N, P, R, S,
T,
X, Y, Z). If the data is not correct prompt user to fix it. Is there
better
way to validate the cells that multiple if ..then .. else?

Set up data validation (Data-Validation) and in the settings tab, set Allow
drop-down to a List. You need to set up a list of valid values somewhere in
your worksheet. Unfortunately data validation is not case sensitive (!) so
in the past I have used the worksheet change event to replace the value
entered to uppercase. You will need to change the references to those cells
you wish to change and test e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Row <= 100 Then
Target.Value = UCase(Target.Value)
End If
End Sub


--
Cheers
Nigel



"
wrote in message
...
I need to check data in a range of 100 cells. The value can be blank or
single character only from these upper case letters (A, B, C, N, P, R, S,
T,
X, Y, Z). If the data is not correct prompt user to fix it. Is there
better
way to validate the cells that multiple if ..then .. else?

Ardus solution is even better - I learn something everyday!

--
Cheers
Nigel



"Nigel" wrote in message
...
Set up data validation (Data-Validation) and in the settings tab, set
Allow drop-down to a List. You need to set up a list of valid values
somewhere in your worksheet. Unfortunately data validation is not case
sensitive (!) so in the past I have used the worksheet change event to
replace the value entered to uppercase. You will need to change the
references to those cells you wish to change and test e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Row <= 100 Then
Target.Value = UCase(Target.Value)
End If
End Sub


--
Cheers
Nigel



"
wrote in message
...
I need to check data in a range of 100 cells. The value can be blank or
single character only from these upper case letters (A, B, C, N, P, R, S,
T,
X, Y, Z). If the data is not correct prompt user to fix it. Is there
better
way to validate the cells that multiple if ..then .. else?