# Adding a range for flashing cells

#### graemeal

##### Active Member
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.

Code:
``````Public a1n2(1) As Variant
Public t As Range

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

In Sheet Module

Code:
``````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``````

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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):

Code:
``````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``````

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

Replies
2
Views
128
Replies
2
Views
127
Replies
9
Views
322
Replies
5
Views
133
Replies
0
Views
152

1,196,317
Messages
6,014,611
Members
441,832
Latest member
tony tessman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back