# Macro vs Data Validation vs Ifs statement

#### VARD

##### New Member
Hi,
I have the following task and I am looking for the optimized solution. Please help.

Problem statement.
User inputs:
B1: wage
B2: income tax percent
B3: social security percent
B4: Age

B5: =B1*(B2+B3)

I need a solution to meet the following:

If Age<30 then B5 <=10000
If Age>=30 then B5<=12000
Otherwise, reset the inputs in B2 and B3 and show a message that explains the limitations of the entry. (B1 isn’t arbitrary, B2 and B3 are)

What will be the easiest and smartest way to do this, having in mind that B5 is a formula that uses percentage inputs from B2, B3, and B1?

I can do this with if statements in the formula bar but first, the message is too long, second, can’t reset B2 & B3 to empty.

Thank you!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Joe4

##### MrExcel MVP, Junior Admin
Welcome to the Board!

What do you mean by this?
If Age<30 then B5 <=10000
If Age>=30 then B5<=12000
Do you mean capping the Wages (B1) by these dollar amount?
Or are you trying to cap the final calculation bu those amounts?

Maybe walk us through an actual example.

#### Joe4

##### MrExcel MVP, Junior Admin
If you are looking to cap the final calculation by those amounts, try this formula:
Excel Formula:
``=MIN(B1*(B2+B3),IF(B4<30,100000,120000))``

#### VARD

##### New Member
Joe4, thank you for reacting.
I guess I didn't specify my problem clearly.

I need to show a warning message to a user when the percentages he/she input result in total of higher than certain amount allowed. As a numeric example:
B1: 500,000 input
B2: 10% input
B3: 10% input
B5: total= 500,000*(0.1+0.1)=100,000 formula
Now, 100,000 exceeds the amount allowed by the law, so I need a message box telling the user that he needs to lower the percentages, and I need to reset those percentages in B2 and B3 to 0.

#### VARD

##### New Member
I have the following code, however it only runs once. I need it to appear again and again until the user changes the percentages, so that B18 is not larger than 10000 (or12000).

Sub contr_limit()
If Range("Age_1") <= 30 And Range("B18").Value >= 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
ElseIf Range("Age_1") >= 31 And Range("B18").Value >= 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
End If
End Sub

#### Joe4

##### MrExcel MVP, Junior Admin
I have the following code, however it only runs once. I need it to appear again and again until the user changes the percentages, so that B18 is not larger than 10000 (or12000).

Sub contr_limit()
If Range("Age_1") <= 30 And Range("B18").Value >= 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
ElseIf Range("Age_1") >= 31 And Range("B18").Value >= 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
End If
End Sub
OK, you are changing your ranges all around on us. Your original example had ranges B1:B5, but now you are saying B18.
I am going off of your original layout, and you can adjust the code as needed.

You can do what you want with a Worksheet_Change event procedure. This is VBA code that is automatically triggered to run when certain watched cells are manually updated.
This must be placed in the appropriate sheet module in order to run automatically.
The easiest way to ensure that is to right-click on the sheet tab name at the bottom of the screen, right-click, select "View Code", and paste this code in the resulting VB Editor window:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim pctRng As Range

'   Enter range to watch for new entries in
Set pctRng = Range("B1:B4")

'   Exit if updated cell not in watched range
If Intersect(Target, pctRng) Is Nothing Then Exit Sub

'   Check for each condition
If Range("B4") >= 30 Then
If Range("B5") > 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
vbRetryCancel + vbCritical, "WARNING"
End If
Else
If Range("B5") > 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
vbRetryCancel + vbCritical, "WARNING"
End If
End If

End Sub``````
This check will run automatically any time a cell in the range B1:B4 is manually updated.

#### VARD

##### New Member
OK, you are changing your ranges all around on us. Your original example had ranges B1:B5, but now you are saying B18.
I am going off of your original layout, and you can adjust the code as needed.

You can do what you want with a Worksheet_Change event procedure. This is VBA code that is automatically triggered to run when certain watched cells are manually updated.
This must be placed in the appropriate sheet module in order to run automatically.
The easiest way to ensure that is to right-click on the sheet tab name at the bottom of the screen, right-click, select "View Code", and paste this code in the resulting VB Editor window:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim pctRng As Range

'   Enter range to watch for new entries in
Set pctRng = Range("B1:B4")

'   Exit if updated cell not in watched range
If Intersect(Target, pctRng) Is Nothing Then Exit Sub

'   Check for each condition
If Range("B4") >= 30 Then
If Range("B5") > 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
vbRetryCancel + vbCritical, "WARNING"
End If
Else
If Range("B5") > 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
vbRetryCancel + vbCritical, "WARNING"
End If
End If

End Sub``````
This check will run automatically any time a cell in the range B1:B4 is manually updated.
By bad, should have been B5. The code worked. Thank you very much!

#### Joe4

##### MrExcel MVP, Junior Admin
You are welcome.
Glad I was able to help!

Threads
1,186,506
Messages
5,958,247
Members
438,346
Latest member
Andrew1234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back