macro to create warning popup if a condition is true

[fingermouse]

Hi,

I've been asked to with find a macro-based solution to the following task, hope someone can help.

On an Excel worksheet, I have a range of cells within a column. For example, A2:A30.

In each of these cells there's a numerical value.

In column B of the same worksheet , I have a drop down list with 4 user options. The list applies to cells B2:B30.

The options are 'Compulsory' Voluntary' 'None' and 'Not applicable'

If a value in a column A cell is less than 250,000 (0 to 24,999) the user cannot select 'Compulsory' in the corresponding cell in column B. if the user attempts to do so, I want a pop-up message which states "threshold not met - please select 'none'. "

is this possible? Any help very much appreciated.

Thanks, Cal.

 

[L. Howard]

Hi Cal, try this in the sheet module

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("B2:B30")) Is Nothing Then Exit Sub
   If Target.Count > 1 Then Exit Sub
   
   If Target.Offset(, -1).Value < 250000 And Target.Value = "Compulsory" Then
     MsgBox "Threshold not met - please select ""none"""
     
     Target.ClearContents
     Target.Activate
     Exit Sub
   End If
    
End Sub

 

[fingermouse]

Works like a dream L. Howard!! Many thanks!

 

[L. Howard]

You're welcome, thanks for the feedback.

Howard