Change Event Code To Run Macro When A Cell Value Changes

[Colin Nicol]

Hi !

I use Excel a lot but don't understand VBA much.

I have looked at a series of Change Event topics and code but can't see what I need.

I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes.

I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...

can anyone help ??

Thanks.

Colin

[mudraker]

This is an example of a worksheet change macro

It adds 1 to the value in c3 every time a1 or b1 is changed

Replace cell refences in red with a single cell or a cell range to suit
Replace Code in Pink with what action you want the macro to take

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Application.Intersect(Target, Range("[COLOR="Red"]a1:b1[/COLOR]")) Is Nothing Then 
        Application.EnableEvents = False 
        [COLOR="Magenta"]Range("c3").Value = Range("c3").Value + 1[/COLOR] 
    End If 
    Application.EnableEvents = True 
End Sub 

[Colin Nicol]

Thanks.....

The code works only when a value is keyed into the range.

It doesn't work where there is a formula in the range ... as I have .. =max(b1:b10).

I have a query on one sheet which will refresh every 15 minutes .... the =max() formula will pickup the most recent Time from the Query and it is this that I need to trigger the firing of the Macro.

Unless there is another way of doing it ?

Colin

[Ger Plante]

In a bit of a rush, but you could try something like this....

Private Sub Worksheet_Change(ByVal Target As Range) 
    Static old_value As Variant 
     
    If ActiveSheet.Range("C7").Value <> old_value Then 
         'a change has occured in cell C7 so do your processing....
        MsgBox "Changing...." 
        old_value = ActiveSheet.Range("C7") 
    End If 
     
End Sub 

HTH

Ger

[Colin Nicol]

Turns out the "Change event" does not work with a formula .....

needs "Change Calculate" !

Colin