Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Me.Range("b2:b30"), Target)

If myRng Is Nothing Then Exit Sub

Randomize

Application.EnableEvents = False

For Each myCell In myRng.Cells

If myCell.Value 0 Then
myCell(1, 5).Value = Rnd
myCell(1, 7).Value = Rnd

End If

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

Next myCell

Application.EnableEvents = True

End Sub

I wasn't sure what you were doing--are Randomize and Randomize2 your own
functions? If they are, you'll have to modify this code--I changed it. You may
want to stay away from naming your function Randomize, since VBA has its own
Randomize statement.



mslabbe wrote:

Thanks a bunch, as it cleared up the error...however, if I paste values at
the same time in that range, the function does not fire...is there away
around that? I did not realize that was happening because of the run-time
error I was getting...

Thanks again

"Dave Peterson" wrote:

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


--

Dave Peterson