software

I have a spreadsheet that is tracking stock prices. The cell with the price
is fed by a DDE link.StockPrice
AIG100
AXP100
AMAT100
AMGN100
AMD100

I am trying to create a visual monitoring tool. I was hoping to have the
cell (ColB) change color (any color will do) everytime the price changes.

For example, if the cell initially is no color, on the first change, color
switches to green, on the next change, back to no color, on the 3rd price
change, back to green etc.

Is this type of thing possible ?

Thank you in advance.

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sRANGE As String = quot;B2:B100quot;
Const nCOLORINDEX As Long = 10 'Green
With Target
If .Count gt; 1 Then Exit Sub
If Not Intersect(.Cells, Range(sRANGE)) Is Nothing Then
With .Interior
.ColorIndex = IIf(.ColorIndex = nCOLORINDEX, _
xlColorIndexNone, nCOLORINDEX)
End With
End If
End With
End SubIn article gt;,
carl gt; wrote:

gt; I have a spreadsheet that is tracking stock prices. The cell with the price
gt; is fed by a DDE link.
gt;
gt;
gt; StockPrice
gt; AIG100
gt; AXP100
gt; AMAT100
gt; AMGN100
gt; AMD100
gt;
gt; I am trying to create a visual monitoring tool. I was hoping to have the
gt; cell (ColB) change color (any color will do) everytime the price changes.
gt;
gt; For example, if the cell initially is no color, on the first change, color
gt; switches to green, on the next change, back to no color, on the 3rd price
gt; change, back to green etc.
gt;
gt; Is this type of thing possible ?
gt;
gt; Thank you in advance.

Thank you very much.

I tried this out but could not get it to work . I have a formula in ColB.
Does that make a difference ?

quot;JE McGimpseyquot; wrote:

gt; One way:
gt;
gt; Put this in your worksheet code module (right-click the worksheet tab
gt; and choose View Code):
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; Const sRANGE As String = quot;B2:B100quot;
gt; Const nCOLORINDEX As Long = 10 'Green
gt; With Target
gt; If .Count gt; 1 Then Exit Sub
gt; If Not Intersect(.Cells, Range(sRANGE)) Is Nothing Then
gt; With .Interior
gt; .ColorIndex = IIf(.ColorIndex = nCOLORINDEX, _
gt; xlColorIndexNone, nCOLORINDEX)
gt; End With
gt; End If
gt; End With
gt; End Sub
gt;
gt;
gt; In article gt;,
gt; carl gt; wrote:
gt;
gt; gt; I have a spreadsheet that is tracking stock prices. The cell with the price
gt; gt; is fed by a DDE link.
gt; gt;
gt; gt;
gt; gt; StockPrice
gt; gt; AIG100
gt; gt; AXP100
gt; gt; AMAT100
gt; gt; AMGN100
gt; gt; AMD100
gt; gt;
gt; gt; I am trying to create a visual monitoring tool. I was hoping to have the
gt; gt; cell (ColB) change color (any color will do) everytime the price changes.
gt; gt;
gt; gt; For example, if the cell initially is no color, on the first change, color
gt; gt; switches to green, on the next change, back to no color, on the 3rd price
gt; gt; change, back to green etc.
gt; gt;
gt; gt; Is this type of thing possible ?
gt; gt;
gt; gt; Thank you in advance.
gt;

Yes, that makes a difference - Worksheet_Change() only fires when a
cell's value is changed manually or via link.

Hard to tell what to change without knowing how your data is input...

In article gt;,
carl gt; wrote:

gt; I tried this out but could not get it to work . I have a formula in ColB.
gt; Does that make a difference ?

Thank you again. The price data in ColB is coming from a quot;DDE Linkquot;. This is
a quot;formulaquot; that reads in streaming data from a data source.

Does that clarify ?

quot;JE McGimpseyquot; wrote:

gt; Yes, that makes a difference - Worksheet_Change() only fires when a
gt; cell's value is changed manually or via link.
gt;
gt; Hard to tell what to change without knowing how your data is input...
gt;
gt; In article gt;,
gt; carl gt; wrote:
gt;
gt; gt; I tried this out but could not get it to work . I have a formula in ColB.
gt; gt; Does that make a difference ?
gt;