Runtime Error

[Carlton Patterson]

Hi

Can someone please tell me why I get a runtime error with the following
code. And could someone please tell me how to resolve it?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
Dim n As Integer
Dim NextTime As Date
If Range("a1", "a2").Value > 7 Then
For n = 1 To 5
With Range("a1").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
With Range("a1").Interior
If .ColorIndex = 3 Then .ColorIndex = 2 Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next
End If
With Range("a1")
..Font.ColorIndex = 3
..Interior.ColorIndex = 2
End With
End Sub


Cheers

Carlton

 

[Norman Jones]

Hi Carlton,

(1) Replace:

With Range("a1")
Font.ColorIndex = 3
Interior.ColorIndex = 2
End With

with:
With Range("a1")
. Font.ColorIndex = 3
.Interior.ColorIndex = 2
End With

(Note the initial . (dot) before Font and Interior.

(2) Replace:

If Range("a1", "a2").Value > 7 Then

with something like:

If Range("A!").Value > 7 Then '(To test only A1)

or

If Range("a1").Value > 7 _
Or Range("A2").Value > 7 Then '(To test if Either A1 or B1>7)

or

If Range("a1").Value > 7 _
And Range("A2").Value > 7 Then '(To test if Both A1 or B1>7)

 

[Carlton Patterson]

Cheers Norman,

I'll give them a go and let you know how I get on.

Thx

Carlton

 

[Carlton Patterson]

Hi Norman,

Can you tell me why the following won't work?

If Range("A1:B5"). Value > 7

Cheers

Carlton

 

[Carlton Patterson]

Hi Norman,

On closer inspection I already have:

With Range("a1")
. Font.ColorIndex = 3
.Interior.ColorIndex = 2
End With


Therefore, I'm not sure what you needed to replace???

Carlton

 

[Norman Jones]

Hi Carlton,

Hi Norman,

Can you tell me why the following won't work?

If Range("A1:B5"). Value > 7

Cheers

Carlton

A multiple-cell range does not have a value although the constituent cells
do.

What is your intention? Do you want the code to run if one, any or all the
designated cells match your test value (>7)?

FWIW, each of the alternatives in my initial response worked for me.

 

[Carlton Patterson]

Hi Norman,

Yes, it is my intention to have the code run if one, any or all the
designated cells match your test value (>7)

And I agree that the second alternative works, but I was just a little
unsure about the first alternative as it appears that I already have
that code.

Cheers

Carlton

 

[Norman Jones]

Hi Carlton,

Yes, it is my intention to have the code run if one, any or all the
designated cells match your test value (>7)

Try:

Dim n As Integer
Dim NextTime As Date
Dim Rng1 As Range, Rng2 As Range, rCell As Range

If Intersect(Target, Me.Range("A1:A5")) Is Nothing _
Then Exit Sub

Set Rng1 = Intersect(Target, Me.Range("A1:A5"))

If Application.Max(Rng1) <= 7 Then Exit Sub

For Each rCell In Rng1.Cells
If rCell.Value > 7 Then
If Not Rng2 Is Nothing Then
Set Rng2 = Union(Rng2, rCell)
Else
Set Rng2 = rCell
End If
End If
Next

With Rng2
For n = 1 To 5
With .Font
If .ColorIndex = 2 Then .ColorIndex = 3 _
Else .ColorIndex = 2
End With
With .Interior
If .ColorIndex = 3 Then .ColorIndex = 2 _
Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next

.Font.ColorIndex = 3
.Interior.ColorIndex = 2

End With

End Sub

 

[Carlton Patterson]

Hi Norman,

Thanks for your efforts mate. However, when I insert a number in any of
the fields between A1:A5 nothing happens.

Carlton

 

[Norman Jones]

Hi Carlton,

I truncated the Sub header line in my post.

The code, which works for me, responds (only) if an entry > 7 is made in
one, or more of the A1:A5 cells.

With the header, the code should read:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
Dim NextTime As Date
Dim Rng1 As Range, Rng2 As Range, rCell As Range

If Intersect(Target, Me.Range("A1:A5")) Is Nothing _
Then Exit Sub

Set Rng1 = Intersect(Target, Me.Range("A1:A5"))

If Application.Max(Rng1) <= 7 Then Exit Sub

For Each rCell In Rng1.Cells
If rCell.Value > 7 Then
If Not Rng2 Is Nothing Then
Set Rng2 = Union(Rng2, rCell)
Else
Set Rng2 = rCell
End If
End If
Next

With Rng2
For n = 1 To 5
With .Font
If .ColorIndex = 2 Then .ColorIndex = 3 _
Else .ColorIndex = 2
End With
With .Interior
If .ColorIndex = 3 Then .ColorIndex = 2 _
Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next

.Font.ColorIndex = 3
.Interior.ColorIndex = 2

End With

End Sub

 

[Carlton Patterson]

Norman,

I don't know what to say. It works brilliantly. I can't thank you
enough.

Cheers mate.

Carlton

 

[Carlton Patterson]

Hi Norman,

I hope you're still around, cos there is something else that I was
hoping you could assist with the program you just compiled for me.

At the moment, the cell flashes if you manually insert a number between
cells A1:A5. However, could you tweak it so that it changes if the value
of the cell changes as a result of, lets say, a formula?

Cheers mate.

Carlton

 

[Carlton Patterson]

Hi Norman,

I hope you're still around, cos there is something else that I was
hoping you could assist with the program you just compiled for me.

At the moment, the cell flashes if you manually insert a number between
cells A1:A5. However, could you tweak it so that it changes if the value
of the cell changes as a result of, lets say, a formula?

Cheers mate.

Carlton

 

[Norman Jones]

Hi Carlton,

What are the formulas in each of the 5 cells? Assuming that cells A1:A5 are
linked to other cells, how are the precedent cells' values set?

 

[Carlton Patterson]

Norman,

The formula is a simple division formula. For example, H2/P2.

Cheers

Carlton

 

[Carlton Patterson]

Norman,

I appreciate the my explanation isn't very discriptive but I don't know
how else to describe the formula. Its basically dividing one number into
another.

Cheers

Carlton

 

[Norman Jones]

Hi Carlton,

[cut]

At the moment, the cell flashes if you manually insert a number between
cells A1:A5. However, could you tweak it so that it changes if the value
of the cell changes as a result of, lets say, a formula?

The following should deal with both manual insertion and with formula
changes :

'=================>>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim Rng1 As Range, Rng1A As Range, Rng1P As Range
Dim Rng2 As Range, Rng3 As Range, Rng3A As Range
Dim Rng4 As Range, Rng5 As Range, rCell As Range

Set Rng1 = Me.Range("A1:A5")

On Error Resume Next
Set Rng1A = Intersect(Target, Rng1)
Set Rng1P = Intersect(Target, Rng1.Precedents)
On Error GoTo 0

If Rng1A Is Nothing And Rng1P Is Nothing Then Exit Sub

If Not Rng1A Is Nothing Then
If Not Rng1P Is Nothing Then
Set Rng2 = Union(Rng1A, Rng1P)
Else
Set Rng2 = Rng1A
End If
Else
Set Rng2 = Rng1P
End If

Set Rng3 = Intersect(Target, Rng2)

On Error Resume Next
Set Rng3A = Rng3.Dependents
On Error GoTo 0

If Not Rng1A Is Nothing Then
If Not Rng3A Is Nothing Then
Set Rng4 = Union(Rng1A, Intersect(Rng1, Rng3A))
Else
Set Rng4 = Rng1A
End If
Else
Set Rng4 = Intersect(Rng1, Rng3A)
End If

For Each rCell In Rng4.Cells
If Not IsError(rCell.Value) Then
If rCell.Value > 7 Then
If Not Rng5 Is Nothing Then
Set Rng5 = Union(Rng5, rCell)
Else
Set Rng5 = rCell
End If
End If
End If
Next

If Rng5 Is Nothing Then Exit Sub

With Rng5
For n = 1 To 5
With .Font
If .ColorIndex = 2 Then .ColorIndex = 3 _
Else .ColorIndex = 2
End With
With .Interior
If .ColorIndex = 3 Then .ColorIndex = 2 _
Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next

.Font.ColorIndex = 3
.Interior.ColorIndex = 2

End With

End Sub
'<<<=================

 

[Carlton Patterson]

Hello Norman,

I haven't had a chance to test the formula. I will test it later and let
you know how I get on.

Thanks ever-so-much.

Cheers

Carlton

 

[Carlton Patterson]

Norman,

You've cracked it. I really don't know how to thank you. You'll most
probably never exactly why this is important to me but suffice to say it
will save me a lot of time and heartache.

Thanks again mate.

I hope someday I'll be able to help you out.

Cheers

Carlton

 

[Carlton Patterson]

Hi Norman,

I hope you're still around. Anyway, I put the program to the test today
during market hours and unfortunately it still will only flash if I
manually insert a number in the cell.

Any help will be greatly appreciated mate.

Carlton