Conditional Validation in Excel

[cherosoullis]

Because I want a conditional validation in excel I decided to do it with VB.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Sheets("MAIN").Range("C5").Value = "A:0-1650" Then
Sheets("MAIN").Range("C7").Value = "> 0"
Sheets("MAIN").Range("C7").Value = "<1650"
else
If Sheets("MAIN").Range("C5").Value = "B:1651-2025" Then
Sheets("MAIN").Range("C7").Value = "> 1651"
Sheets("MAIN").Range("C7").Value = "<2025"
End If
End Sub

This code does not work. I dont know why. Please help me.
The A and B are values from a drop down list. According with this selection I want to validate C7 cell with minimum and maximum values and stopping wrorg values

[Simon Lloyd]

Your code works fine, if it see's the value in C5 then change C7 the trouble you sre trying to change C7 to 2 different values at the same time!

Regards,
Simon

[moa]

You have "else" and then an "if" on the next line. You just need an "Elseif".

[moa]

Also, I wouldn't put this code in your Change event for the worksheet as it goes into an infinite loop.

[Bob Phillips]

Use data validation oijn C7 with an allow type of Custom, and a formula of

=OR(AND(C5="A:0-1650",C7>0,C7<1650),AND(C5="B:1651-2025", AND(C7>1651,C7<2025)))

[Bob Phillips]

BTW, what about 1650?

[Charlize]

BTW, what about 1650?
> 1649
< 2026 (second then)
or
>= 1650
<= 2025 (second then)

and 1651 ? (on second then)

Charlize

[Bob Phillips]

> 1649
< 2026 (second then)
or
>= 1650
<= 2025 (second then)

and 1651 ? (on second then)

Charlize

What is your point? The OP's code doesn't cater for 1650, so I was asking where does that go?

[Charlize]

to XLD : no point of me. I just thought that 1651 and 2025 weren't also included in the else clause. That's all.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 And Target.Column = 3 Then
Select Case Cells(5, 3).Value
Case "A:0-1650"
If Target.Value >= 0 And Target.Value <= 1650 Then
Exit Sub
Else
MsgBox ("Please, insert value from 0 to 1650")
Target.Select
Target.Delete
End If
Case "B:1651-2025"
If Target.Value > 1650 And Target.Value <= 2025 Then
Exit Sub
Else
MsgBox ("Please, insert value from 1651 to 2025")
Target.Select
Target.Delete
End If
End Select
End If
End Sub

[cherosoullis]

thanks