Multiple condition message box

[Sephaerius]

Hello,
I'm seeking help on some VBA code. Should be fairly simple but I'm a little stumped. Here is what I'm looking for in plain language:

If cell K1 = 3 and if the value of any cell in column A is < 80000 or is > 86666, then a msg box will pop up stating "Invalid range".

The user will be manually inputting 5 digit numbers into column A, but if they've selected cell K1 to equal 3 from a dropdown menu (which the user also does), then values less than 80000 or greater than 86666 are out of range, and the user should be alerted.

Does this make sense?

Thank you,

[duncandhu]

Hi Sephaerius

Try this in the Worksheet_Change event in the sheet you are using:

Please Login or Register  to view this content.

[Sephaerius]

Hi duncandhu,
Thank you this looks like it's on the right track. EDIT: disregard

[Sephaerius]

Thank you this is working pretty good,

I do have additional questions. If K1=3 prior to the user entering the numbers, it works, however if the user enters the five digit numbers first then changes K1 to 3, it does not trigger. Is there a way to get it to do this? Ideally it should clear out the invalid numbers per the same criteria, and alert the user.

Also, one troubleshooting issue. I had recorded a macro button to reset my spreadsheet, however it now gives me a Run-time error 13 Type mistach on the "If Target.Value <>..." line of the code. My macro was recorded to clear the contents of Column A and reset K1 back to 1. I'm not sure why this is causing a problem.

Thank you!

[duncandhu]

K1 has to be 3 before the user starts typing.

The problem with triggering it after the user enters the numbers is that you have to check every cell with content at each change. This will slow down the whole operation with large volumes of data.

Not sure about the type mismatch.. the macro should be modified however:

Please Login or Register  to view this content.

Let me know how this goes.

Afterthought:
Are you only checking values for column A? I think I could be able to implement a solution to check the values after changing cell K1=3 without having to check every cell every time a cell gets changed.

Thanks
Duncan

[Sephaerius]

Hi Duncan,
This has been working well, and I wanted to thank you for your help. I'm sorry for the delay in responding, I had since become side tracked on other projects. Also, I was able to resove my macro issue with some tinkering.

I do have another question. Another "bug" has arisen due to my lack of specifying. The "invalid range" code applies to the whole worksheet, and it needs to only apply to Column A. The issue I've discovered is that it will also trigger this issue if a value in Column B is edited. Column B acts as a multiplier for Column A. The user enters the 5 digit number in Column A, then (if needed) enters the multiplier in Column B. The default text in Column B is "1", however if more than one units are needed they may need to change it.

So the long and the short of it is that I would like this code to only apply to only values entered into Column A. Any thoughts?

Thank you again.

[duncandhu]

Hi Seph,

This should work, it only checks values for column A. (Target.Column = 1)

Thanks,
Duncan

Please Login or Register  to view this content.