yes, you could change it to a worksheet_change macro. this would have
to be put in the sheet module for the sheet you're working on:

right click on the sheet tab & choose "view code".

then add this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range


Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("q1:q10")

If Target = Qrange Then
Application.EnableEvents = False

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & " tttttttttttttttttttttttttttttttttt")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(rMsg & " Do you want to" _
& " delete this record?", vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c
End If

Application.EnableEvents = True

End Sub

hope that helps!
:)
susan



On Jan 26, 8:01*pm, DDD wrote:
Thank you it works.

Though, when i tested this, i had to run the macro manually when i made the
value "1" display in the cell (in the q column)

& had to run the macro manually again when when i changed "1" to "2"

is there any way i can run this macro automatically when the excel file is
opened & let it run continually, so that i dont have to repeat running the
macro over & over



"Susan" wrote:
ok, i'm going to use a small range of q1:q10, for these purposes
only. *you will have to change the range to suit what you need. *also,
i'm not familiar with 2007; i assume this code will work on 2007 but
don't know that for sure. *Compiled but not tested - save a copy of
your workbook before running the code:

Option Explicit

Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("q1:q10")

For Each c In Qrange
* * *If c.Value = "" Then
* * * * * 'do nothing
* * *ElseIf c.Value = 1 Then
* * * * * Set rMsg = WS.Range("a" & c.Row)
* * * * * MsgBox (rMsg & " tttttttttttttttttttttttttttttttttt")
* * *ElseIf c.Value = 2 Then
* * * * * Set rMsg = WS.Range("a" & c.Row)
* * * * * * If MsgBox(rMsg & " Do you want to" _
* * * * * * * & " delete this record?", vbYesNo _
* * * * * * * , "Make a decision.") = vbYes Then
* * * * * * * * *WS.Range("c" & c.Row).ClearContents
* * * * * * * * *WS.Range("e" & c.Row).ClearContents
* * * * * * * * *WS.Range("g" & c.Row).ClearContents
* * * * * * *End If
* * * End If
Next c

End Sub

hope it works!
:)
susan

On Jan 26, 9:43 am, DDD wrote:
I have an excel system that is used on excel 2007 & 2003. im just saying this
if it is important in some way

In the Q column i have a formula, in short displays 1 or 2 or nothing (As in
"") when certain circumstances are met.

I want to have a message box that displays when a number appears (1 or 2)

If 1 appears, i want the message to display the contents of the cell in the
same row in the A column, & state message
"tttttttttttttttttttttttttttttttttttttttttttt" .

If 2 appears i want the message to display the contents of the cell in the
same row in the A column & ask to delete the record with options yes or no to
click on.
If No is clicked, nothing happens. If yes is clicked i wanted the contents
of certain cells to be cleared (Cells on the same row in coLumns C, E, & G )

Sorry if this is not clear, if you have any questions please ask.- Hide quoted text -

- Show quoted text -