macro - pop up if value entered is higher than expected

[GVR40]

Hi Team,

I need some help with a Macro. it is probably something simple for you guys, however my programming isn't up to scratch at this stage.

What I need is if a value is entered in a particular cell that is over say $100 then a pop up comes up that says "are you sure".

I need this for the range of cells D10:J35

Cheers for your help
Craig

 

[AlphaFrog]

If you have Excel 2007 or later, you could use the Data Validation feature to pop up a Warning or Information only type message.

Apply data validation to cells

 

[GVR40]

only have excel 2003 unfortunatley

 

[AlphaFrog]

• Right-click on the sheet tab
• Select View Code from the pop up menu
• Paste the code below in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D10:J35"), Target) Is Nothing Then
        If Target(1).Value > 100 And IsNumeric(Target(1)) Then
            MsgBox "Are you sure?", , "Input Greater Than $100"
        End If
    End If
End Sub

 

[GVR40]

works in 2003 as well. thanks for your help!

 

[Blade Hunter]

Right click on the tab and select "View code"

Paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target.Text) Or Target.Rows.Count > 1 Then End
If Target.Column > 3 And Target.Column < 11 And Target.Row > 9 And Target.Row < 36 And Target.Value > 100 Then
    If MsgBox("Are you sure", vbYesNo, "Large value detected") = vbNo Then
        Target.Formula = ""
    End If
End If
End Sub

Cheers

Dan

 

[Michael M]

Data Validation will do this in 2003 as well...is that what you are saying ?

It can be done with using custom and in the formula use

=D10<100