Msgbox if you input a value smaller than another

hI

1. Highlight range A1:A20
2. Go to Data, and press on Validation button in Data Tools group. Dialog Box will appear
3. In that dialog box, select Custom from dropdown
4. Type this formula there: =$B1<$A1
5. Press Ok.

NOTE: You can type your own error message in Alert tab,,,

Data Validation is actually the "easier" method.. but I think Contaminated's formula should be either

=$A1<$B1

or

=$B1>$A1

not

=$B1<$A1

If A is less than B (or B is greater than A), show the message. If A is greater than B (or B is less than A) don't show a pop-up.

@ Paul
Please see screenshot

Yup.. you're right. I'm having a bad day with Data Validation apparently. For some reason I was thinking DV failed when the expression was True. ::smacking self::

And basically it would be much easier to do this with VBA with the command MsgBox,

I can't really understand, why to use VBA, when there is built-in option?

With VBA, you will have too loop each cell in range. And the more the range the more the time it will take to loop. .....IMO.... (maybe there is another way in VBA w/o using loops). But Data Validation it takes seconds...

Can you please post a sample wb???

FYI, there is another Alert Style, like your MsgBox - Information. You just need to select thrid item of dropdown in Alert type. There are OK, Cancel and Help buttons.

Read this thread at leisure: http://www.excelforum.com/the-water-...o-do-this.html
Funny enough...

Hello tsioumiou,

This macro will display an alert for all worksheets when the value in "A1:A20" is less than the value in "B1:B20".

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Dim Msg As String
  Dim Rng As Range
  
    Set Rng = Sh.Range("A1:A20")
    
    If Target.Cells.Count > 1 Or Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    If Target.Value < Target.Offset(0, 1).Value Then
       MsgBox "The minimum value is " & Target.Offset(0, 1).Value
    End If
    
End Sub

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Hello ,

To make it sheet specific, you need to compare the desired sheet name to the current sheet in the event. Here is the modified code. Change the sheet name below in bold to what you will be using.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Dim Msg As String
  Dim Rng As Range
  
    Set Rng = Sh.Range("A1:A20")
    
    If Sh.Name <> "Sheet1" Then Exit Sub

    If Target.Cells.Count > 1 Or Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    If Target.Value < Target.Offset(0, 1).Value Then
       MsgBox "The minimum value is " & Target.Offset(0, 1).Value
    End If
    
End Sub

Hello tsioumiou,

Here is the revised code for "D6:D20" and "J6:J20". Column "J" is offset from column "D" by 6 columns to right.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Dim Msg As String
  Dim Rng As Range
  
    Set Rng = Sh.Range("D6:D20")
    
    If Sh.Name <> "Sheet1" Then Exit Sub

    If Target.Cells.Count > 1 Or Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    If Target.Value < Target.Offset(0, 6).Value Then
       MsgBox "The minimum value is " & Target.Offset(0, 6).Value
    End If
    
End Sub