Message boxes?

[esmcinty]

I have a value in cell D8. How can I create a pop up message if D8 is less than cell B17 OR greater than cell B21?

 

[Marcelo Branco]

Hi,

Wouldn't you prefer to highlight the cell when one of the conditions is met?

If so you can use Conditional Format like this
Select D8
Home
Conditional Format > New Rule > Use a formula to determine...
and enter this formula
=OR(B17>D8, D8>B21)
Format button
Fill --> yellow (for example)
ok, ok


HTH

M.

 

[esmcinty]

I would rather have a pop up box, a format change would be a last resort. Thanks for the help!

 

[Marcelo Branco]

I would rather have a pop up box, a format change would be a last resort. Thanks for the help!

Try this

Right-click in the sheet-tab, pick View Code and paste the sub below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D8")) Is Nothing Then
        If Range("D8") < Range("B17") Or Range("D8") > Range("B21") Then
            MsgBox "some message"
        End If
    End If
End Sub

When a value is entered in D8 if one of the conditions is met a pop-message will appear.

M.

 

[esmcinty]

Will this still work if the value is the result of a formula, and not a value entered in?

 

[Marcelo Branco]

Will this still work if the value is the result of a formula, and not a value entered in?

No. The Worksheet_Change event is not triggered by a formula, but we can, maybe, find a workaround. What formula are you using in D8?

M.

 

[Marcelo Branco]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D8").Precedents) Is Nothing Then
        If Range("D8") < Range("B17") Or Range("D8") > Range("B21") Then
            MsgBox "some message"
        End If
    End If
End Sub

M.

 

[esmcinty]

Excellent!! Thank you so much!! Is there any way to format the text in that pop up box?? Change the color or size?

 

[esmcinty]

In the same spreadsheet in a different place, I manually enter a number. Is there a way to make it reference another tab within the same workbook to find the same number, and go to the right 8 columns (not counting the column with the number) and pop up a message box with the text that is in the same row 8 columns over??

 

[shg]

Nicely done, Marcelo.

Is there any way to format the text in that pop up box?? Change the color or size?

Not using MsgBox, unless you change the appearance (font name, size, style) in Control Panel, which would affect all other applications.

The other alterrnative is a userform, but that sounds like overkill.