Pop up a message in vba

[trapeze2011]

I have below vba, which works fine with one cell but if I want to extend it to range of cells it bring run time error.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Private Sub Worksheet_Calculate()
If Me.Range("B9").Value <= 0 Then _
MsgBox "Leave is finished!"
End Sub

</code>suppose I want to have the range of B9:CZ9
can anybody suggest me a solution?
P.S: B9 has formula returning a value

 

[Norie]

Do you want to check each cell I'm the extended range individually or do you just want to check if there are any cells in the range with a value less than or equal to zero?

 

[trapeze2011]

I want to check all the cells, those cells sum the values from other sheet which I do enter their values manually. I just want to have a pop up message when doing manual data entry in other sheets.

 

[Norie]

This will check each cell in the range individually.

Private Sub Worksheet_Calculate()
    For Each cl In Me.Range("B9:CZ9")
        If cl.Value <= 0 Then 
            MsgBox "Leave is finished!"
        End If
    Next cl 
End Sub

 

[trapeze2011]

Many thanks for the reply, It seems to have a small problem, suppose in the first cell B9, i got the zero or negative result which bring pop up message, when want to to the data entry for the B10, even it is not zero or less bring the pop up message again.

 

[Norie]

That code doesn't look at B10 but it will be triggered every time you calculate the sheet.