MACRO IS NOT COMPLETE

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hi , I want to add line in macro (please see below) that if I have
text of "ZERO
BUDGET"
appear in any cell of Range("K25:K62") then I should get
Message box appear that there is Zero budget. What should i put in
macro below that do the work which i mentioned above

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Const WS_RANGE As String = "B25:D62"
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
 
For Each cell In Range("K25:K62")
If cell.Text = "ZERO BUDGET" Then
MsgBox "Zero Budget", vbInformation
Exit For
End If
Next cell
 
Here's another way.

If WorksheetFunction.CountIF(Range("K25:K62"),"ZERO BUDGET") > 0 Then
MsgBox "I found Zero Budget in this range."
End if


HTH,
JP
 
Back
Top