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

 

[wsjackman]

your program will only make the first one less than B1 blink.
if you want all less than B1 to blink, remove the "exit for" line in the code in this case each one less than b1 will blink 10 times each in sequence from top to bottom.

If you want blinks only when a value in a row is changed
the code needs to be changed.

the best code will be different depending on whether the values in D and E are entered manually or are the the result of formulas,

you need a trigger which specifiies whcih row to look at.

 

[asad]

I got this code from schielrn and it is working beautifully.

Private Sub Worksheet_Calculate()
Dim R As Range
Dim arr() As String
For Each R In Range("$A$1:$A$10")
ReDim Preserve arr(counter)
If R.Value < Range("$B$1") Then
arr(counter) = R.Address
counter = counter + 1
End If
Next R
R2 = Range(arr(0)).Address
For i = 1 To counter - 1
R2 = Union(Range(R2), Range(arr(i))).Address
Next i
Call Blinker(Range(R2))
End Sub

Thanks a lot for your time, patience and help.

 

[asad]

your program will only make the first one less than b1 blink.
If you want all less than b1 to blink, remove the "exit for" line in the code in this case each one less than b1 will blink 10 times each in sequence from top to bottom.

If you want blinks only when a value in a row is changed
the code needs to be changed.

The best code will be different depending on whether the values in d and e are entered manually or are the the result of formulas,

you need a trigger which specifiies whcih row to look at.

this is wonderful.

I now have what i asked for in the first place.

Thanks a lot again.

 

[asad]

OUt of curousity, just wondering, what sort of code would I need to blink one cell in the range if values in column D and E are entered manually but it change the value in column A by a formula in column A.

 

[wsjackman]

something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
If Target.Column <> 4 And Target.Column <> 5 Then Exit Sub
Set r = Cells(Target.Row, 1)
If r.Value < Range("$B$1").Value Then Call Blinker(r)
End Sub
[end code]
 
entered in the code for the worksheet would be one option
my excel is running slow when this macro is in operation but it may be related to my cat making the computer display be sideways (He is talented and doesn't like the computer)

 

[asad]

THANKS A LOT.
I did a bit of change in your code and now it looks like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
If Target.Column <> 4 And Target.Column <> 5 Then Exit Sub
Set r = Cells(Target.Row - 1, 1)
If r.Value < Range("$B$1").Value Then
Call Blinker(r)
End If
End Sub

Because your code was looking at the row which gets selected after the user anters a value in a cell eg. D5. Then, normally the user will hit enter which makes cell D6 highlighted and as a result Row 6 was being looked at by your code. So, I that's the reason I changed the code to look at the row one before the selected row which is the one normally user would have entered a value.

 

[wsjackman]

you must have the move after enter selected.
I almost never use it so do not think about it. sorry

 

[asad]

you must have the move after enter selected.
I almost never use it so do not think about it. sorry

I tried using the same with worksheet calculate event but it did not work.
I tired to set the range with target row and column 1 but it gives me an error

Private Sub Worksheet_Calculate()
Dim R As Range
Set R = Cells(Target.Row, 1)
If R.Value < Range("$B$1").Value Then
Call Blinker(R)
End If
End Sub

This is what I did and it gives an error with

Set R = Cells(Target.Row, 1)

highlighted in yellow.

Is there anything you can help me with????????

 

[wsjackman]

try

Private Sub Worksheet_Calculate()
Dim R As Range
dim rr as integer
rr=actvecell.row
Set R = Cells(rr, 1)
If R.Value < Range("$B$1").Value Then
Call Blinker(R)
End If
End Sub

 

[asad]

I am really surprised at the ease you solve the problems.

Thanks a lot.