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.

Use Data Validation. See:

http://www.contextures.com/xlDataVal06.html
--
Gary''s Student - gsnu200721


"Colin Hayes" wrote:

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.

In article , Gary''s
Student writes
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.

^_^

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??
--
Gary''s Student - gsnu200721


"Colin Hayes" wrote:

In article , Gary''s
Student writes
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.

^_^

In article , Gary''s
Student writes
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

Colin

Select the range in column B and DataValidationAllowCustom.

=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

On Tue, 15 May 2007 00:19:45 +0100, Colin Hayes
wrote:

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D: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.
--
Gary''s Student - gsnu200721


"Colin Hayes" wrote:

In article , Gary''s
Student writes
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

In article , Gary''s
Student writes
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("D: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