Conditional Validation in Excel (1 Viewer)

[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 drop down list. According with this selection I want to validate C7 cell with minimum and maximum values and stopping wrorg values

 

[rat_b76]

Excel Issue

Hi,

Firstly, are your security settings at such a level to enable macros?

Secondly, I ran a similar code through Excel, using a drop down box with 2 options and did not have a problem. The issue that I noticed with your code is that you are entering a cell value, but then you are changing it? For example, in your If statement you state firstly once the worksheet is changed:

Sheets("MAIN").Range("C7").Value = "> 0"

Then you are changing this value to:

Sheets("MAIN").Range("C7").Value = "<1650"

This will most likely send your statement in to a fit (infinite loop) as the worksheet is changing, so it triggers the if statement over and over...

More detail regarding the problem and exactly what you are trying to achieve may help get more responses.

I hope this gives you some assistance..

Cheers

 

[cherosoullis]

I want after my selection the C7 cell to accept only a range of values

 

[rat_b76]

Hi,

If you want to validate a cells contents then you are using the incorrect VBA code, as what you are instructing Excel to do is to change the cell value to:

">0", is it that you want to validate that cell C7 is >0, if this is the case then you may wish to try something like:

if Sheets("MAIN").Range("C7") > 0
then
(your true code goes here)

By putting the >0 in quotations you're stating that this is a text value not a integer? The other problem is that you're not telling it to do anything in the If statement? You're simply trying to check the value of C7 but what happens if these conditions are not met??

For your consideration.

Cheers

rat_b76