Flash color changes

  • Thread starter Thread starter jonny
  • Start date Start date
J

jonny

I have a list of prices in an excel sheet. Periodically the prices change (as
they are impacted by a live feed). Is there any way that I can get any change
in any price to briefly show up in my excel spreadsheet as a 'flash' e.g., a
change in color (preferably gree for up and red for down).
 
jonny,

Possible, yes, you can do that quite easily, but it will require calculate event code, as well as a
range of cells to store historicaly values (and possibly links to the cells with the live feeds.) I
can't comment on the live feed that you are using, but I'm not certain that all live feed addins
will trigger the calculate event. Copy this code, then right-click the sheet with the live feeds,
select "View Code" and paste the code into the window that appears. If you get a message box, note
which event was fired, then you can delete the code:

Option Explicit
Dim boolChange As Boolean
Dim boolCalc As Boolean

Private Sub Worksheet_Calculate()
If boolCalc = False Then
MsgBox "The Calc Event has been fired."
If MsgBox ("Continue checking?", vbYesNo) = vbNo Then boolCalc = True
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If boolChange = False Then
MsgBox "The Change Event has been fired."
If MsgBox ("Continue checking?", vbYesNo) = vbNo boolChange = True
End If
End Sub

(If neither fires, enter a linking formula (like =A1) somewhere, and answer Yes when asked the first
two times....)
Once you figure out which event works, then, for an example with live feeds in column A, you can use
code like

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("A:A").Copy
Range("X:X").PasteSpecial xlPasteValues
Application.EnableEvents = False
End Sub

Then select all of column A, and conditional formatting with the first condition using the formula

=A1>X1

formatted for green, and the second condition

=A1<X1

formatted for red.

HTH,
Bernie
MS Excel MVP
 
Back
Top