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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***