Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("B2:B30")) Is Nothing Then Exit Sub

Application.EnableEvents = False

If Target.Value 0 Then
Target(1, 5).Value = Randomize(Rnd)
Target(1, 7).Value = Randomize2(Rnd)
End If

If Target.Value = 0 Then
Target(1, 5).Value = 0
Target(1, 7).Value = 0
End If

Application.EnableEvents = True
End Sub




mslabbe wrote:

Worksheet Change Error

I was trying to get a cell change run a macro...got it with this below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then
If Target.Value 0 Then
Target(1, 5).Value = Randomize(Rnd)
Target(1, 7).Value = Randomize2(Rnd)
End If

If Target.Value = 0 Then
Target(1, 5).Value = 0
Target(1, 7).Value = 0
End If
End If
Application.EnableEvents = True
End Sub

Now, if I want to highlight more then one cell in the range B2:B30, and
paste something or delete the contents, I get the old Run-time error '13':
Type mismatch.

Does anyone know how to get around this?

BTW, I was helped getting this formula from this disscussion group...you all
are awesome at what you know.

Thanks in advance

--

Dave Peterson