Hi Karl

try the following code to use with a Double Click event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim myrange As Range, cell As Range

Set myrange = Range("D4, I4, N4") '<=== Extend as necessary
For Each cell In myrange
If cell.Value 50 Then
MsgBox "Error in cell " & cell.Address & vbCr _
& "Value too high"
End If
Next
End Sub

--
Regards
Roger Govier

"karl" wrote in message
...
Hi Roger,

Suppose I want to use that code for ten cells in one worksheet. I have
tried
to do so but couldn't accomplish much.
I have attached an example as well.
http://www.freefilehosting.net/download/3ibkh
thanks

"karl" wrote:

hi roger,

thank you very much!! it works great.


"Roger Govier" wrote:

Hi Karl

I was not thinking correctly.
As there is a formula in D4, which will get recalculated when there is
a
change on the sheet, then you could use

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4").Value 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

If for any reason the cell doesn't get calculated each time, then you
could
force volatility by changing the formula to
=C1/C2+NOW()-NOW()

--
Regards
Roger Govier

"karl" wrote in message
...
here we go Roger: http://www.freefilehosting.net/download/3hbeh
thanks.

"Roger Govier" wrote:

Hi Karl

We need an event to trigger the macro.
From what you had said previously, the value in D4 is a formula,
based
upon
entries in other cells
Let me know what the formula is in D4, so I can see which cells it
is
dependent upon.
--
Regards
Roger Govier

"karl" wrote in message
...
Roger, Thank you. I later realized that I had to double click on
the
cell
in
order to get the popup window.
please help me to have a popup window as soon as the wrong data is
entered,
without having to return to the cell and double clicking on it-
just as
the
"ERROR" shows up in d6, in my original example.

thank you in advance.

"Roger Govier" wrote:

Hi Karl

The NG reader has wrapped it onto 2 lines.
In the code window, hit the delete button at the end of line 1,
and
that
will bring it all back to one line and it will change from Red to
Black.

or use the code posted below with a proper line break in it

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Range("D4").Value 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

--
Regards
Roger Govier

"karl" wrote in message
...
Hi Roger,
I must be doing something wrong. I hit alt f11 and pasted the
code.
the
first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal
Target
As
Range, Cancel As Boolean)) is all red when i paste it.
thanks.

"Roger Govier" wrote:

Hi Karl

I now understand.
You cannot use DV error box, unless it is applied to that
cell.

You could incorporate some VBA code to check it, and bring up
an
error
message, but you need an event to trigger it.
The following code would be triggered if you double click
anywhere
on
the
sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel
As
Boolean)
If Range("D4").Value 50 Then
MsgBox "Error in cell D4. Value too high"
End If
End Sub

Copy the code above
Right click on Sheet tabView CodePaste into the white pane.
Alt+F11 to return to Excel

--
Regards
Roger Govier

"karl" wrote in message
...
the reason I don't want to validate D4 is that it is a sum
of two
numbers
and
I won't be able to select that cell directly. I hope I
answered
your
question.

"Roger Govier" wrote:

Hi Karl

I don't understand why you are not prepared to have data
Validation
on
cell
D4.

Apply DV and set it to allow Whole Number between Minimum 0
and
Maximum
50
Set the message on the error alert tab to something like
"The value in this cell must be less than 50"

There will be no dropdown on the cell, but if anyone tries
to
enter
a
value
greater than 50, they will be prevented and your message
will
pop
up.
--
Regards
Roger Govier

"karl" wrote in message
...
hello,

i need to set up an "indirect" error alert using DATA
VALIDATION.
please
refer to the following link
http://www.freefilehosting.net/download/3h8gj
details are provided in the uploaded file.

thank you,
karl