excel code

Quote from 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
___________________________
I entered osorico code, but cells were not updating to green, only red each time a cell was changed.:confused:
More...

What if he has more than 500 rows? What if he has more than 1 column? You used Workbook_change. Where's validation that the changes are being made on the appropriate sheet?

As I said earlier, statics are fine, but performance is affected by how many.

As for my code not working. If you read the comments you'll know why. It's a framework for something robust, not operational other than for testing 1 change at a time.

Osorico

EDIT: I just good-looked your code.

Your code will update all 500 cells every time the event triggers. In this case every time ANY cell on ANY worksheet changes, regardless of which cells you are interested in. MAJOR MAJOR BOTTLENECK! Won't be able to handle normal-speed multiple updates and just forget about multiple fast moving updates.

EDIT 2: I think I got a relatively elegant solution. I'll post WORKING code in a few minutes.
 
why don't u just use the conditional formating, its under the format toolbar, then u can set any colour, any range, under any greater than/ less than conditions u want, why have to do vba, using a missile to shoot a bird
 
Quote from dtan1e:

why don't u just use the conditional formating, its under the format toolbar, then u can set any colour, any range, under any greater than/ less than conditions u want, why have to do vba, using a missile to shoot a bird
More...

Because conditional format uses simple logic. if x > 0 then green. In this case we need if x > y, where y is a previous value, which will be different from cell to cell.
 
Quote from osorico:

Because conditional format uses simple logic. if x > 0 then green. In this case we need if x > y, where y is a previous value, which will be different from cell to cell.
More...

u can still do x>y, say if y is in a cell above just click the cell abv instead of entering "0," but u got to click the small box on the right first, unless u r doing some kind of array or something complex i'm not sure what
 
Quote from dtan1e:

u can still do x>y, say if y is in a cell above just click the cell abv instead of entering "0," but u got to click the small box on the right first, unless u r doing some kind of array or something complex i'm not sure what
More...

That's the whole issue. y is the previous value in the cell being formatted. x is known... it just overwrote y! That's why we need to cache the y value.

Not to worry, I've got a nice solution. Almost ready to post.

Osorico
 
Working code!! Elegant solution if I say so myself. :)

NOTE: Created in XLXP. The crux of this uses the Range.Comment object. This may not be backwards compatible.
A Note object exists in previous versions but is deprecated.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'Code should work as is

'Id really like to see more upfront validation.
'For performance, the constants should be moved to module level
'and there are a couple of other code changes that I can think of that should be made.
'
'keep in mind... THIS EVENT FIRES EVERY TIME A CHANGE OCCURS ON *THIS* WORKSHEET
'the more code optimizations that can be made the better.
'

    Dim previous_value As Variant

    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 the lookup.
    '
    'It uses the Target.Comment object. Caveat: comments are text!
    'First we check to see if there is comment in a numeric-like format.
    'If not, it's likely this is the first iteration for this target.
    'So we seed our previous value with a usuable but Badf00d value for compare.
    'Badf00d will(should) always produce green on compare.
    '
    'If there is a valid comment, it is THE PREVIOUS VALUE, and we compare.
    '*****************************************************
    On Error Resume Next
    previous_value = Target.Comment.Text
    If Not IsNumeric(previous_value) Then
        previous_value = -666
    End If
    Err.Clear
    On Error GoTo 0
    '*****************************************************

    'compare and colorize
    Select Case Target.Value
        Case Is > CDbl(previous_value)
            Target.Interior.Color = vbGreen

        Case Is < CDbl(previous_value)
            Target.Interior.Color = vbRed
        Case Else
            Target.Interior.PatternColorIndex = xlPatternNone
    End Select


    '**************************************
    '
    'here is the PREVIOUS VALUE CACHE UPDATE
    '
    'Comments are picky. They have to be deleted or cleared before overwriting.
    'I chose clear. Its faster then delete which internally involves a collectionof objects.
    'No sense destroying, adjusting the collection and then creating another in the same place.
    '
    Target.ClearComments

    'Now "overwrite" the "previous" with the "current". Current is previous on next iteration.
    Target.AddComment.Text CStr(Target.Value)
    '
    '**************************************

    'PRETTY FRICKIN COOL!!!

    'Osorico 5/9/07

End Sub

Please let me know how it works out and what tweaks you want/need.

Osorico :)
 
thanks osorico for that snippet. it will probably take me a few hours or days to completely figure out and understand what is happening there, and to modify the code for personalization.
 
You must log in or register to reply here.
Back
Top