Changing a range

graemeal

Active Member
Joined
May 17, 2007
Messages
316
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
try changing

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

to

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

FarmerScott
 

graemeal

Active Member
Joined
May 17, 2007
Messages
316
Platform
  1. Windows
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
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 17, 2007
Messages
316
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,049
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 ??
 

Watch MrExcel Video

Forum statistics

Threads
1,127,573
Messages
5,625,594
Members
416,121
Latest member
MrBuzz

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top