Coloring based on Worksheet Change Event

[HJA14]

Hi all,

I have an Excel-file that consists of so-called tick data of stocks. The prices of these stocks change a lot and are displayed in column C. I would like to write a macro that gives the cell of each stock a color based on the price change.
The color depends on whether the price difference is an increase or a decrease.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub
    Dim NewPrice, OldPrice
    Dim intersection As Range
    Set intersection = Intersect(Target, Range("C:C"))
    OldPrice = Target.Value
    
    If Not intersection Is Nothing Then
        Application.EnableEvents = False
        
        With Target
            NewPrice = .Value
            Application.Undo
            OldPrice = .Value
            .Value = NewPrice
        End With
    


        Cells(Target.Row, 4).Value = OldPrice
    
        If Cells(Target.Row, Target.Column).Value < Cells(Target.Row, 4).Value Then
            Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 10066431
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
            
        ElseIf Cells(Target.Row, Target.Column).Value = Cells(Target.Row, 4).Value Then
             Cells(Target.Row, 2).Select
             With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
            
        Else
            Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 11919289
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            Cells(Target.Row, 9).Value = Cells(Target.Row, 9).Value + 1
        
        End If
    End If
    
Application.EnableEvents = True


End Sub

Question 1: Why is macro not changing the color of the cells?
Question 2: What happens when this macro runs, for example sparked by a change in Cell B2, and, while running, a different cell in column B changes? Will this macro run again and, more importantly, each time a cell changes?
Question 3: Will other cells in column B change in the first place while this macro runs. Because my macro includes the following (and essential loop-preventing) lines

If Not intersection Is Nothing Then
Application.EnableEvents = False
Do something
Application.EnableEvents = True

Thank you

 

[My Aswer Is This]

How is this happening?
You said:

The prices of these stocks change a lot and are displayed in column C​

Are these changes being made manually or as a result of a formula or some other event?

 

[HJA14]

How is this happening?
You said:

The prices of these stocks change a lot and are displayed in column C​

Are these changes being made manually or as a result of a formula or some other event?

The values of the cells change because of the underlying formula. Nothing is done manually

 

[HJA14]

Ignore question 1 and the

[COLOR=#333333]End Sub[/COLOR]

at the beginning of the macro please!

 

[My Aswer Is This]

Sheet change events only activate when a physical change is made to the sheet.

 

[My Aswer Is This]

Or some other script causes the change. A formula change will not activate the change event script.