AAO- macro to compare two cell data and display a pop up message when one exceeds the other.

[Former Member]

Hi Experts ,

I have tried an approach in AAO , in which i am comparing two cell data using a MAcro and displaying the error message when one of the cell value exceeds the other.

Let suppose i have two cells in my AAO that is displayed as part of a query displayed in the layout.

C1 and C2 .

Now i want an error message to be displayed for the user whenever he tries to enter any value in C1 that is less than the value in C2.

Means , at the time of saving the data , i need a check to be introduced in form of a message box that disables the user to save data.

if i enter 10000 in C1 and there is 20000 in C2 already, then the user gets a message stating "Error".

i tried the following code myself :

Sub Macro2

If Range("C1").value < Range("C2").value then

msgbox ( "Error")

Enndif

End sub

But for this i have to either create a button to call the macro or just run it everytime.

Is there any way where as soon as user enters value the comparison is done and the pop up appears?

Any reply will be highly appreciated.

Thanks ,

Ayushi

[former_member288194]

Hi Ayushi,

A solution without using a macro:

You can simply use Data Validation.

- Select cell C1

- Data Validation

- In the allow box, select "Whole number"

- In Data, select "greater than or equal to"

- In Minimum, enter "=C2"

- In "Error alert" tab, enter your own error message

Regards,

Pierre

[Former Member]

Dear Pierre,

How to use data validation in the AAO?

Thanks ,

Ayushi

[Former Member]

Hi Pierre,

Will the solution work everytime i make changes to C1 or C2?

Thansk ,

Ayushi

[former_member288194]

Hi Ayushi,

What do you mean by AAO?

In Excel, data validation can be found in the "Data" tab of the Excel ribbon.

I you apply a data validation to C1, the check will only be performed when you change the value in C1. If you want, you can also add a data validation to C2.

Pierre

[Alecsandra]

Hi Ayushi,

Replace your code with below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C1").Value < Range("C2").Value Then

MsgBox ("Error")

End If

End Sub

The Worksheet_change will run the code every time you make a change somewhere on the worksheet.

Rgds

Alecsandra

[Former Member]

Dear Alecsandra ,

Thanks for the reply , i am trying the solution provided by you .

Lets see if it works for me or not.

Thanks,

Ayushi