How to pop up message box if cell greater or less than a specific value in Excel?
If a cell is greater or less than another cell, I need to pop up a message box to alert in Excel. How to solve this problem in Excel? This article, I will talk about some codes for dealing with it.
Pop up a message box if cell value in a column is greater or less than another cell
Pop up a message box if cell value in a cell is greater or less than another cell
Pop up a message box if cell value in a column is greater or less than another cell
Supposing, I have a specific number 100 in cell A1, now, when the number entered in column D is greater than 100 in cell A1, a message box should be popped out to remind the user.
Here, the below VBA code can help you to finish it, please do with the following steps:
1. Go the worksheet that you want to use.
2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:
VBA code: pop up a message box if cell value in a column is greater or less than a cell
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
Dim xC As String
Dim xWSName As String
Dim xA As String
xC = "D:D"
xWSName = "Sheet1"
xA = "A1"
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub
xNum = (Sheets(xWSName).Range(xA).Value)
If (Target.Value) > (Sheets(xWSName).Range(xA).Value) Then
MsgBox Prompt:="The entered number is greater than cell A1, please enter again! ", Title:="Kutools for Excel"
End If
End Sub
Note: In the above code, Sheet1 is your worksheet that you want to use; A1 is the cell value you want to compare with; D:D is the column that you want to compare with cell A1.
 |
 |
 |
3. Then, save and close the code window, from now on, a message box will be popped out if you enter a number in column D which is greater than 100, see screenshot:
Pop up a message box if cell value in a cell is greater or less than another cell
If you want a message box is popped out when a cell value is greater or less than another cell, please apply the below code:
1. Go the worksheet that you want to use.
2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:
VBA code: pop up a message box if cell value is greater or less than a cell
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error GoTo ExitSub
Dim One As Long
Dim Two As Long
One = Range("A1").Value
Two = Range("D1").Value
If Not (Application.Intersect(Range("A1:D1"), Target) Is Nothing) Then
If (One < Two) Then
MsgBox "The value in cell D1 cann't be greater than cell A1!", vbInformation, "Kutools for Excel"
End If
End If
ExitSub:
End Sub
Note: In the above code, A1 and D1 are the two cells that you want to compare with.
3. Then, save and close the code window, when entering a value which is less than the value in cell A1, a message box will be popped up as below screenshot shown:
More relative pop up message box articles:
- Create Pop Up Message Box When Opening An Excel File
- In certain case, you may want a message box always popup to remind you what you need to do first when you open a specific workbook, this can help you remember to do the most important thing in your daily work. And this article, I will talk about how to create a pop up message box when opening an Excel file.
- Pop Up Message Box If Cell Value Changes Within A Range In Excel
- Sometimes you need to remind yourself of the changes within a range in a certain worksheet. So a pop up message box will do you a favor if cell value has changed in a certain range. You can achieve it with a VBA method in this article.
- Pop Up Message Box When Clicking On A Certain Cell In Excel
- This article aims to show you the method of popping up message box when clicking on a certain cell in Excel.
- Create A Yes No Message Box Before Running A Macro In Excel
- In Excel, you can directly trigger a macro by pressing the F5 key or click the Run button in the Microsoft Visual Basic for Applications window. But, sometimes, you may want to create a message box that will ask the user if they want to run the macro. If yes, then continue running the code, if no, stop running the code. This article, I will talk about how to create a yes no message box before running a macro in Excel.
- Create A Message Box Timer To Auto Close The Message Box After Certain Time
- You may use VBA script frequently to solve problems while using Excel workbook. And there will be message boxes popped up during the script running. In some cases, you would like some specified message boxes to be closed automatically after a certain time rather than close it manually. In this article, we will show you method of creating a message box timer to auto close the message box after certain time in Excel.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
