0

I need a message box to appear when the value in cell G27 exceeds the value in cell K13. I require this to show up as soon as cell G27 is filled. I have tried the following macro, but it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("G27") > Range("K13") Then
        MsgBox "error"
    End If
End Sub

Any help is highly appreciated!

Community
  • 1
  • 1
mahathi a
  • 33
  • 4

2 Answers2

0

Is this what you are trying? (UNTESTED) Also it is understood that you have this code in the Sheet Code area and not a module :)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("G27")) Is Nothing Then _
    If Range("G27").Value > Range("K13").Value Then MsgBox "error"

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

More about Worksheet_Change HERE

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

You can try this as well (Data Validation):

Use With Sheets("WorksheetName"). before Range("G27").Validation if used under Private Sub Workbook_Open() Or, under any standard module. Use as below if used under Private Sub Worksheet_Activate() 

With Range("G27").Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
    Operator:=xlLessEqual, Formula1:="=K13"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "Input Title"
    .ErrorTitle = "Error Title"
    .InputMessage = "Input Msg"
    .ErrorMessage = "Error Msg"
    .ShowInput = True
    .ShowError = True
End With

FYI: Data Validation can be used without vba i.e directly from excel. You have option of AlertStyle (Information,Warning,Stop) and Input Title. See http://office.microsoft.com/en-001/excel-help/apply-data-validation-to-cells-HP010342173.aspx

ZAT
  • 1,347
  • 7
  • 10