hello. is anyone familiar with coding in excel? i would like to code a cell to change colors when the value of that cell changes. example if current.price > previous.price then color.font(green) in other words how can i cache the current.price into a previous.price variable into memory to check the next price against? thank you.
=IF(H10>K10,"HOLD","SELL") then you can change the color , in this case, cell turns either green or red .. then udertools >conditional format to change to color you want .. no charge jake
If you open up the visual basic editor and place the following code under "This Workbook", it will do what you want. Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Static price As Double If Range("d3") > price Then Range("d3").Interior.Color = vbGreen End If If Range("d3") < price Then Range("d3").Interior.Color = vbRed End If price = Range("d3") End Sub
Pro Trader your the man ! thanks I copy pasted for future reference with my excel files I LOVE EXCEL !!! jake
Since the OP has not commented I hesitate on posting, BUT... The Workbook_Change event solution as written works only on one cell. Yet this event triggers on ANY change on ANY sheet in the workbook. Better would be to use WorkSheet_Change event. But even that triggers on ANY change on the specific sheet. Note: Both events occur AFTER the cell data has been changed. There is no built-in BEFORE event. Now then, the OP states i would like to code a cell to change colors when the value of that cell changes. My *guess*is he has a column (or multiple columns) of cells that change, maybe via DDE. The events above can be used if this is the case, but the code must be much more robust. The after change data (price) and some sort of identifier (cell address) must be persisted per cell for comparing. After compare, the persisted data must then be updated with the after change data. Use of static vars is a good solution with few cells being tracked. You could use a static array for many. But large amounts of static data is not a good idea. Maybe better to write it out to a hidden "lookup" worksheet. Osorico
thanks for the code and responses. i mistakenly asked about a 'single' cell instead of an entire column range. this seems to be a lot more complicated... i initially thought that it could be possible to substitute a ("$col") instead of a single cell ("d3"), but that does not seem to be the case. however that code by protrader did work fine for a single cell. performance seems good for single cell but i won't know how it would perform for many many cells during market hours without the optimizations that osorico mentioned. today right after the fomc announcement, some quotes were lagged like it was 1929.
Here's another snip. The framework maybe for what you want. Using WORKSHEET change event Code: Private Sub Worksheet_Change(ByVal Target As Range) 'original var 'Static price As Double Dim lookup_result As Double Const LASTPRICECOLUMN$ = "B" 'for column number to column letter conversion. First 26 columns only. Const ALPHA$ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" 'verify we are processing numeric data. Or whatever type of data expected If Not IsNumeric(Target.Value) Then Exit Sub 'stupid pet trick to get the Column Letter 'allows filtering changes we don't need to process If Mid$(ALPHA, CStr(Target.Column), 1) <> LASTPRICECOLUMN Then Exit Sub '***************************************************** ' 'here is where lookup value to compare should go, based on...Target.Address is my rec. 'of course, the method of storage of lookup data is yet to be determined. ' '***************************************************** 'just a test value. changed manually until lookup code is written lookup_result = 123 Select Case Target.Value Case Is > lookup_result Target.Interior.Color = vbGreen Case Is < lookup_result Target.Interior.Color = vbRed Case Else Target.Interior.PatternColorIndex = xlPatternNone End Select '************************************** ' 'here is where lookup UPDATE would go 'updatelocation = Target.Value ' '************************************** 'original code 'If Target.Value > price Then ' Target.Interior.Color = vbGreen ' End If 'If Range("d3") < price Then ' Range("d3").Interior.Color = vbRed ' End If 'price = Target.Value End Sub If you need further help let me know. Osorico
option to extend original code to column: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Static price(500) As Variant dim i as integer 'example for column "D" make length est for max number of changes For i = 1 To 500 If Cells(i, "D") > price(i) Then Cells(i, "D").Interior.Color = vbGreen End If If Cells(i, "D") < price(i) Then Cells(i, "D").Interior.Color = vbRed End If price(i) = Cells(i, "D") Next i End Sub ___________________________ Osorico, I tried your code, but cells were not updating to green, only red each time a cell was changed. Actually, I opened another file and tried it again, but this time it did work ... some times. I would change a cell and it would work, but then i would lower it a few times and there was no change to red. Is there a decision threshold? Not sure why