Runtime Error
 

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


*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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 B17)

or

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


---
Regards,
Norman



"Carlton Patterson" wrote in message
...
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


*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Cheers Norman,

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

Thx

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Norman,

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

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

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Carlton,

"Carlton Patterson" wrote in message
...
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.


---
Regards,
Norman



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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


---
Regards,
Norman



"Carlton Patterson" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Norman,

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

Carlton



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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


---
Regards,
Norman



"Carlton Patterson" wrote in message
...
Hi Norman,

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

Carlton



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Norman,

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

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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?

---
Regards,
Norman



"Carlton Patterson" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Norman,

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

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Carlton,

"Carlton Patterson" wrote in message
...
[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
'<<<=================

---
Regards,
Norman

Runtime Error
 

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


*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Norman,

I think it might have something to do with the following:

f Not IsError(rCell.Value) Then
If rCell.Value 1 Then


Maybe its because its waiting for the value to change???

Carlton



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Carlton,

In a test workbook, the code worked for me.

Are your precedent cells on the same sheet as the A1:A5 cells?

Your previous posts gave no clue as to the intended purpose of the flashing.
However, if, as it now seems, it is to highlight stockmarket price
fluctuations or some such, why not dispense with coded mini-flashing and use
XL's inbuilt conditional format feature?

Flashing may have some superficial appeal, but I doubt that it *really*
provides any increased functionality. Consider, for example, that a
momentarry distraction at an unpropitious moment. and you will miss the
flashes completely!

If you want to send me a copy of your workbook - after deleting/replacing
any sensitive data - I will have a look .

(replace dot and remove each X) :

nXorman_jXones@btXinternetDOTcom

---
Regards,
Norman



"Carlton Patterson" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Cheers Norman,

I'll send you a copy of the workbook with an explanation.

Carlton


*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Carlton,

Paste a copy of the complete failing code into a reply.

---
Regards,
Norman



"Carlton Patterson" wrote in message
...
Norman,

I think it might have something to do with the following:

f Not IsError(rCell.Value) Then
If rCell.Value 1 Then


Maybe its because its waiting for the value to change???

Carlton



*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Carlton,

Try this a/c instead:

nXorman_jXones@btXconnectDOTcom

(Replace DOT and remove each X)

---
Regards,
Norman



"Carlton Patterson" wrote in message
...
Cheers Norman,

I'll send you a copy of the workbook with an explanation.

Carlton


*** Sent via Developersdex http://www.developersdex.com ***

Runtime Error
 

Hi Norman,

I tried sending you an email but I get a message that you don't have a
btinternet.com email account.

Do you have another email address?

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***