Adding a range for flashing cells

[graemeal]

Hi, the below code makes cell A1 and A2 flash either red or green depending on if the number in it become greater or less. Green for increase and red for decrease.

I would like to add more cells, as in a range. Preferably B5:B9 and C5:B9. If that is too difficult maybe B3:B12

Thank you kindly

Windows 7 - Excel 2007



In Standard Module.

Public a1n2(1) As Variant
Public t As Range

Public Sub revintcol()
t.Interior.ColorIndex = -4142
Set t = Nothing
End Sub

In Sheet Module

Private Sub Worksheet_Calculate()
If Cells(1, 1) > a1n2(0) Then
    Cells(1, 1).Interior.Color = vbGreen
    ElseIf Cells(1, 1) < a1n2(0) Then Cells(1, 1).Interior.Color = vbRed
    ElseIf Cells(2, 1) > a1n2(1) Then Cells(2, 1).Interior.Color = vbGreen
    ElseIf Cells(2, 1) < a1n2(1) Then Cells(2, 1).Interior.Color = vbRed
End If
a1n2(0) = Cells(1, 1)
a1n2(1) = Cells(2, 1)
st = Timer + 0.2
Do Until Timer > st
Loop
Range("A1:A2").Interior.ColorIndex = -4142

End Sub

 

[Teeroy]

Hi graemeal,

I think this is what you are after, but I'm not sure as the code that you provided didn't quite work the same as your description.

Try, in the Sheet Module (in place of whan you had):

Dim lastValue

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sColour As String, Rng As Range
Set Rng = Application.Union(Range("B5:B9"), Range("C5:B9"))
If Application.Intersect(Target, Rng) Is Nothing Then Exit Sub
If Target.Value > lastValue Then
    sColour = vbGreen
ElseIf Target.Value < lastValue Then
    sColour = vbRed
Else
    Exit Sub
End If
Target.Interior.Color = sColour
st = Timer + 0.2
Do Until Timer > st
Loop
Target.Interior.ColorIndex = -4142
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lastValue = Target.Value
End Sub

 

[graemeal]

Hi Teeroy,

Thank you kindly, It works great by a enter strokeon that cell. I did forget to mention that the cells that change colour are fired from a differant cell. As in the the value in that cell is not changed by entering a value and then hitting enter. B5 value is cahnged from say F5 eg formuala in F5 would be =F5. The colour change works fine when there is a enter stroke on that cell.

kind regards