Changing a range

graemeal

Active Member
I would like to change the range from A1:A2 to B5:C9. Changing the range at the bottom has no effect. I have very limited knowledge on how code works.

Thanks

WIN7 Excel 2007

In 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

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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

farmerscott

Well-known Member
try changing

Code:
``Range("A1:A2").Interior.ColorIndex = -4142``

to

Code:
``Range("[COLOR=#ff0000]B5:C9[/COLOR]").Interior.ColorIndex = -4142``

FarmerScott

graemeal

Active Member
Thanks but tried that first with no result. Seems code has to added and some removed elsewhere.

I forgot to mention that the code causes cells A1 and A2 to flash red or green depending on whether the figure in that cell rises or drops but the change in that cell has to be implemented from another cell ie A1 has a =A4 formula in it. When you change the cell value via A4 the A1 cell flashes. If you enter a number in A1 and hit enter it does not flash which is why it is supposed to do.

farmerscott

Well-known Member
Hi,

sorry I did not read your post correctly...

So you want to change when the figures change in A1 and A2 to now cover all the cells in the range from B5 to C9?

FarmerScott

graemeal

Active Member
Hi farmerscott,

Yes I just want to change the range. Pretty much done and dusted. I have a couple of codes that I can work with and pretty much sorted it out myself. I day trade the stock market and want to know if the market depth buy side or the sell side are being hit and colour will help.

Thank you kindly

Graeme

Michael M

Well-known Member
Hi Graeme
The code works fine for me .....a number in either A1 or A2 makes the cell flash.
What formula is in A4

Also, are you saying you want to change the code to use B5 and C9, instead of A1 and A2 ??

Replies
6
Views
133
Replies
7
Views
196
Replies
3
Views
81
Replies
3
Views
99
Replies
0
Views
147

1,127,870
Messages
5,627,359
Members
416,245
Latest member
Xterminat

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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