Can please any body know that where and what macro code I can add in
macro below that when ever Text of "ZERO BUDGET" appeares by fromula
in any cell of Range ("K25:K62") then MsgBox should appear saying that
"ZERO BUDGET IN AGRESSO" . can please someone show me the code mix in
the macro below intead of just writting the code


MACRO:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Const WS_RANGE As String = "B2562"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value <> "" And _
Me.Cells(.Row, "C").Value <> "" Then
If IsError(Application.Match(Me.Cells(.Row, "O").Value,
Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION"


End If
End If
End With
End If
Set MyRange = Range("F25:F62")
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value,
Range("AB1:AC9995"), 2, False)
On Error Resume Next
For Each c In MyRange
If c.Address <> Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
On Error GoTo ws_exit
If Target.Value <> "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Const WS_RANGE As String = "B2562"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value <> "" And _
Me.Cells(.Row, "C").Value <> "" Then
If IsError(Application.Match(Me.Cells(.Row, "O").Value, Columns(27),
0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION"
End If
End If
End With
End If
If Application.CountIf(Me.Range("K25:K62"), "ZERO BUDGET") > 0 Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation, "INFORMATION"
End If
Set MyRange = Range("F25:F62")
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value,
Range("AB1:AC9995"), 2, False)
On Error Resume Next
For Each c In MyRange
If c.Address <> Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
On Error GoTo ws_exit
If Target.Value <> "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"K" <(E-Mail Removed)> wrote in message
news:5618435e-0efb-4150-aefd-(E-Mail Removed)...
> Can please any body know that where and what macro code I can add in
> macro below that when ever Text of "ZERO BUDGET" appeares by fromula
> in any cell of Range ("K25:K62") then MsgBox should appear saying that
> "ZERO BUDGET IN AGRESSO" . can please someone show me the code mix in
> the macro below intead of just writting the code
>
>
> MACRO:-
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim MyRange As Range
> Const WS_RANGE As String = "B2562"
> On Error GoTo ws_exit
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If Me.Cells(.Row, "B").Value <> "" And _
> Me.Cells(.Row, "C").Value <> "" Then
> If IsError(Application.Match(Me.Cells(.Row, "O").Value,
> Columns(27), 0)) Then
> MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION"
>
>
> End If
> End If
> End With
> End If
> Set MyRange = Range("F25:F62")
> If Target.Cells.Count = 1 Then
> If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
> If IsNumeric(Target) Then
> budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value,
> Range("AB1:AC9995"), 2, False)
> On Error Resume Next
> For Each c In MyRange
> If c.Address <> Target.Address Then
> If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
> budget = budget + c.Value
> End If
> End If
> Next c
> On Error GoTo ws_exit
> If Target.Value <> "" Then
> Target.Offset(0, 5).Value = budget
> Else
> Target.Offset(0, 5).Value = ""
> End If
> End If
> End If
> End If
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>