adjust code for message box

[ste33uka]

Could anyone change the following code so when the alert message box is activated it would add the value of 100 to column X for row of alert ?
and message would dislay the values for cells H ,I ,J and K for the row of alert

So example z32 = yes , message is displayed for the value of cells h32 i32 j32 k32
and cell x32 would = 100 ?

        Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
For Each cell In myRange
Evaluate (cell)
If StrComp(cell, "Yes", vbTextCompare) = 0 Then
Msg = MsgBox("" & vbCrLf & " " & Range("h32").Value & vbCrLf & Range("i32").Value & vbCrLf & Range("j32") & Range("k32").Value & vbCrLf & Range("l32"), vbExclamation)

End If
Next


End Sub

 

[JLGWhiz]

See if this is what you want.

Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub

 

[ste33uka]

See if this is what you want.

Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub

That looks exactly what i want, but i dont get any message box when cell = yes and 100 does not go to column x

 

[ste33uka]

See if this is what you want.

Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub

Thanks alot its working now, appreciate it

 

[JLGWhiz]

Don't know why yours is not working. This is the test set up with values entered in columns H:K

TestBase.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	a	b	c	d
2	a	b	c	d													100		yes
3	a	b	c	d
4	a	b	c	d
5	a	b	c	d													100		yes
6	a	b	c	d
7	a	b	c	d													100		Yes
8	a	b	c	d
9	a	b	c	d
10	a	b	c	d
11	a	b	c	d													100		Yes
12	a	b	c	d
13	a	b	c	d
14	a	b	c	d
15	a	b	c	d
Sheet1

this is the message box for each row with "yes" in column z.

 

[ste33uka]

Don't know why yours is not working. This is the test set up with values entered in columns H:K

TestBase.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	a	b	c	d
2	a	b	c	d													100		yes
3	a	b	c	d
4	a	b	c	d
5	a	b	c	d													100		yes
6	a	b	c	d
7	a	b	c	d													100		Yes
8	a	b	c	d
9	a	b	c	d
10	a	b	c	d
11	a	b	c	d													100		Yes
12	a	b	c	d
13	a	b	c	d
14	a	b	c	d
15	a	b	c	d
Sheet1

this is the message box for each row with "yes" in column z.

Thats strange works for me

 

[JLGWhiz]

Thanks alot its working now, appreciate it

You're welcome,
regards, JLG