How to give an error message if a cell value entered is larger than permitted

[Colin Hayes]

HI

In column A I have a row of figures.

In column B , I am entering figures which must be equal to or lower than
those shown in column A , comparing cell-for-cell A1 to B1 , A2 to B2
and so on.

What I need to do is to have a Stop message box pop up when the figure
entered in B is larger then the figure showing in A , asking that the
number be re-entered.

Can someone help?

Thanks.

 

[Guest]

Use Data Validation. See:

http://www.contextures.com/xlDataVal06.html

 

[Colin Hayes]

Use Data Validation. See:

http://www.contextures.com/xlDataVal06.html

Hi

Yes I did try validation , but I need to apply this to a very large
number of cells across several worksheets , and the validation procedure
would need to be repeated for each and every one. There seems no way to
duplicate automatically the validation rules without laboriously
re-entering , as my cells contain formulae and don't therefore satisfy
the 'cells with the same settings' criteria.

If I could highlight all the cells to be affected , and also the tabs
for each worksheet , and then apply the validation criteria to them all
at once then that would be fine. It doesn't appear however that excel
allows this.

I think a short piece of code with a box would be neater and more
powerful.

^_^

 

[Guest]

Using code is an interesting idea. I'll check it out tomorrow. In the mean
time do you want a single column in several sheets??

 

[Colin Hayes]

Using code is an interesting idea. I'll check it out tomorrow. In the mean
time do you want a single column in several sheets??

Hi

Yes - it's just some code to check that the number being entered for
example in D3 is not larger than the one in B3. A stop box with a custom
message would be perfect. The same would apply to the same columns in
each sheet in the workbook.

Thanks for your help.



Best Wishes

 

[Gord Dibben]

Colin

Select the range in column B and Data>Validation>Allow>Custom.

=B1<A1

Error message "Value in Column B cannot be greater than Column A"

You could also set it up with an Input Message warning before user enters
anything.


Gord Dibben MS Excel MVP

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D"), Target) Is Nothing Then
Exit Sub
End If
If IsEmpty(Target.Offset(0, -2)) Then
Exit Sub
End If
If Target.Value <= Target.Offset(0, -2).Value Then
Exit Sub
End If
MsgBox ("don't exceed value in column B")
Application.EnableEvents = False
Target.Clear
Target.Select
Application.EnableEvents = True
End Sub

This should go in Worksheet code for any sheet you want to validate.

 

[Colin Hayes]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D"), Target) Is Nothing Then
Exit Sub
End If
If IsEmpty(Target.Offset(0, -2)) Then
Exit Sub
End If
If Target.Value <= Target.Offset(0, -2).Value Then
Exit Sub
End If
MsgBox ("don't exceed value in column B")
Application.EnableEvents = False
Target.Clear
Target.Select
Application.EnableEvents = True
End Sub

This should go in Worksheet code for any sheet you want to validate.

Hi

OK thanks very much for that. Much appreciated.



Best Wishes


Colin