Conditional blinking cell

[asad]

I found this code from Dave in this forum and I liked it vey much. I tried to change it to suit my need but it didn't work. I will paste here the first portion of the code that I changed

Private Sub Worksheet_Change(ByVal Target As Range)
'Test the value of the specified cell
If Target.Address = "$A$3" Then
If Target.Value >= 12 Then Call Blinker(Target)
End If
End Sub

I changed it to:

Private Sub Worksheet_Calculate()
Dim R As Range
For Each R In Range("$A$1:$A$10")
If R.Value < "$B$1" Then
Call Blinker
Exit For
End If
Next R
End Sub

As I don't have any formal education in VB editing, I don't even know that this is the correct way of doing it. Can you please look at it and correct it for me?

The other part of code that goes in standard module is unchanged. But I will post it here just for your ref:

Public Sub Blinker(ByVal rng As Range)
'Causes the cell "rng" to blink 10 times
Dim myCounter As Integer
Do Until myCounter = 10
With rng.Interior
If .ColorIndex = 6 Then
.ColorIndex = xlNone
Else
.ColorIndex = 6
End If
Pause (0.2)
myCounter = myCounter + 1
End With
Loop
End Sub
Public Sub Pause(ByVal dblSecs As Double)
'Pauses the execution of code for the specified number of seconds
Dim Start
Start = Timer
Do While Timer < Start + dblSecs
DoEvents
Loop
End Sub

Thanks in advance

<!-- / message --><!-- / message -->

 

[Smitty]

Without testing, I'd assume your problem is here:

If R.Value < "$B$1" Then

All you're doing there is referencing text.

Try:

If R.Value < Range("$B$1").Value Then

HTH,

 

[asad]

Hello Smitty,
Thanks for the reply. But that didn't work. I am actuially getting a message "Argument not optional" and the part "Call Blinker" gets highlighted.

 

[wsjackman]

change

If R.Value < "$B$1" Then
Call Blinker
Exit For
[exit code]
 
to
 
[code]
If R.Value < "$B$1" Then
Call Blinker(R)
Exit For
[exit code]
 
you need the arguement (range) for the blinker sub

 

[asad]

Thanks for that. It works as in that it has made the cell blink.
BUT, the problem now is that this code is making cell A1 blink even if the condition is true in A3 or A4 or some thing else. I wanted the cell with the value to blink i.e. if A3 has value than B1 then A3 should blink not A1.
Any suggestions????????????

 

[wsjackman]

could you post your current code?

the Smitty correction should have selected the right cell for blinking

If R.Value < range("$B$1").value Then
Call Blinker(R)
Exit For
[exit code]

I should have added it.

 

[asad]

I will post the codes here now

Public Sub Blinker(ByVal rng As Range)
'Causes the cell "rng" to blink 10 times
Dim myCounter As Integer
Do Until myCounter = 10
With rng.Interior
If .ColorIndex = 6 Then
.ColorIndex = xlNone
Else
.ColorIndex = 6
End If
Pause (0.2)
myCounter = myCounter + 1
End With
Loop
End Sub
Public Sub Pause(ByVal dblSecs As Double)
'Pauses the execution of code for the specified number of seconds
Dim Start
Start = Timer
Do While Timer < Start + dblSecs
DoEvents
Loop
End Sub

And the worksheet code is this:

Private Sub Worksheet_Calculate()
Dim R As Range
For Each R In Range("$A$1:$A$10")
If R.Value < Range("$B$1").Value Then
Call Blinker(R)
Exit For
End If
Next R
End Sub

But it still having the same problem.
It is still making first cell in the whole range to blink. I want the single cell in the range to blink if the condition is true.
E.G. if range A1:A10 calculate the sum of D1:D10 and E1:E10, and only A3 has value more than $B$1 then A3 only should blink.

 

[wsjackman]

Are you sure A1 is Greater than B1?

the only way A1 should be blinking with your code is if A1 is less than B1

check what =B1-A1 says.

 

[asad]

I checked again and this is what I found out.

IF in the whole range the cells are less than $B$1, then only the top cell will blink.
If A1 or/and A2 is greater than $B$1 then A2 or A3 as the case may be will blink.
What I want it to do is that when I change the value in e.g. D4 then tha values in all other cells remain unchanged but A4 will change to different number. I want the code to work on this A4 if it is less than $B$1 then it should blink. But all the other cells should stay normal because I didn't change any other cell.
I hope I am making some sense.

 

[asad]

I also tried when A1 is greater than B1 and A2 is less and A3 is greater again. NOw in this case, when I changed the value in D7 to get the value in A7 less than B1 it still is making A2 blink. It should have made A7 blink not A2.