Multiple condition message box

[Scott B]

Hello,
I'm seeking help on some VBA code. Should be fairly simple but I'm a littlestumped. 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.

Column A will be blank until the user types in a 5 digit number, so the code only needs to trigger if the number typed into a cell in column A is outside of the aforementioned range.

So to sum it up once again, I'm looking for a msgbox if the following criteria is simultaneously met-
*If K1 = 3 (meaning that K1 equaling anything other than 3 wouldn't causethe msgbox)
*And values typed in column A are less than 80000 or are greater than 86666(meaning that values typed in between 80000 - 86666 wouldn't cause the msgbox.)

Does this make sense?

Thank you,

 

[benmcclave]

Scott,

There are 2 ways that I came up with to address this need. First, you could use a macro on the worksheet's Change event. I have copied code to accomplish this below.

The other way might be easier, though. You could use a combination of datavalidation and conditional formatting to achieve the same effect. To do so, try the following:

1. Select column A
2. Open the Data Validation dialog box and choose "Custom"
3. For the formula enter: =IF($K$1=3, OR(A1<80000, A1>86666)=FALSE, TRUE)
4. Complete any other info you wish in the data validation dialog (i.e. input message, error alert etc.)
5. Next, go to the conditional formatting settings and use the formula option.
6. For the formula enter: =AND($K$1=3, OR(A1<80000, A1>86666), A1<>"")
7. Select appropriate formatting to highlight invalid data.

The effect of the data validation and conditional formatting is that if K1 = 3, users will be alerted whenever invalid data is entered into column A.. In the case that invalid data already exists in column A and K1 is laterchanged to 3, all invalid data will be highlighted.

If you would prefer the macro route, try this one:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Or Target.Address = "$K$1" Then
If Range("K1") = 3 Then
If WorksheetFunction.Min(Range("A:A")) < 80000 Or WorksheetFunction..Max(Range("A:A")) > 86666 Then
MsgBox "Values in column A must be between 80000 and 86666!", vbExclamation + vbOKOnly, _
"Invalid Data"
End If
End If
End If

End Sub