excel code

[dividend]

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.

 

[cashmoney69]

Visual basic script is the lang you need to know. I used to know it, but not anymore.

 

[phil1424]

=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

 

[Pro_Trader720]

If you open up the visual basic editor and place the following code under "This Workbook", it will do what you want.

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

 

[phil1424]

Pro Trader your the man !
thanks I copy pasted for future reference with my excel files
I LOVE EXCEL !!! jake

 

[Pro_Trader720]

Quote from phil1424:

Pro Trader your the man !
thanks I copy pasted for future reference with my excel files
I LOVE EXCEL !!! jake

If you like that, you should see my statements

 

[osorico]

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

 

[dividend]

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.

 

[osorico]

Quote from dividend:

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.

Here's another snip. The framework maybe for what you want. Using WORKSHEET change event

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

 

[dtrader98]

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