Macro to run only when a perticular cell value changes

Hi Everyone
So I have a Macro that is only executed when a certain cell value is changed. In my case its (Cell M2) in (sheet 2).
The value in (Cell M2) is the current year, I used the function =YEAR(NOW()). I want my SaveMe1 macro to run every time a new year begins, aka when the the value in cell M2 changes . I am assuming since I am using the function =YEAR(NOW()), when the next year rolls around it will give me the new year, seeing that it gives me the current year right now.

PS: I checked the formatting on it and its general, not date or Text or integer. , I even did a quick MsgBox Cells(2, "M") and I get 2013 as a pop display. So far so good.

For the purpose of testing, since I cant wait an year to see if it actually works, I changed the year function namely, =YEAR(NOW()) to minute function namely, =MINUTE(NOW()). I get my minutes displayed in cell and once again it is formatted as general. I close the workbook and reopen after a few minutes have gone by, and as expected the new minute shows up, but to my disappointment the macro does not get executed.

now I tried placing the Worksheet_change macro in my ThisWorkbook, nothing
I tried placing the macro in my sheet2 ,once again nothing. Also The macro that i want to execute is saved under one of the modules.

Here is the code

Option Explicit
I tried the following heading and code

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$M$2"
'Insert your code here
Call SaveMe1
Case Else
End Select

And then I tried the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Target.Address
Case "$M$2"
'Insert your code here
Call SaveMe1
Case Else
End Select
End Sub

Not sure if there is a difference.
I would appreciate your assistance, I am really frustrated.

Hopefully I was clear, if not please let me know. Eagerly looking forward to your replies.

Regards
Angsome

Hi Angsome

This is from http://www.cpearson.com/excel/Events.aspx

For the Change event in particular, it should be noted that this is triggered when a cell is changed by user action or by other VBA code, but is not raised if the value of a cell is changed as a result of formula calculation.

Hi Angsome

Place this Code in your Workbook Open Event

Option Explicit
Private Sub Workbook_Open()
    With Sheets("Sheet2").Range("M2")
        .Value = Year(Now)
    End With
End Sub

You may have an issue if you open the Workbook at Midnight on December 31...

Hi Jaslake
Thank you for the prompt reply.
Maybe I should give you more info
The macro I want to execute when the year changes is a macro that created a backup copy of my existing workbook. So basically its a marco that prompts for SaveAs with a predefined SaveAs name and location. The only issue now is that every time I open the original Workbook the macros prompts for SaveAs, seeing that it assigns Cell M2 with the current year and re-assigns cell M2 with the current year each time I open the Workbook. As far as excel is concerned it considers that as change event and therefore prompts for the SaveAs.
I only want the macro to run once a year and again when the year changes, in other words when the user opens the workbook again in 2013 nothing happens, since a back up has already been saved, but when he opens the workbook in the new year it prompts the user to save a copy again.

Is there a way we can do that, hopefully I am not asking for too much.
I do appreciate your efforts
Regards
Angad

Maybe something like this would help, what do you think

Option Explicit
Private Sub Workbook_Open()
    With Sheets("Sheet2").Range("M2")
           If .Value = "" Then
              .Value = Year(Now)
           Else If .Value < Year(Now) Then
              .Value = Year(Now)
    End With
End Sub

Hi Angad

Please use Code Tags around your Code...it's a Forum Requirement. To do so, in your Original Post with Code, highlight your code and click the # icon at the top of your post window.

I'd suggest a similar approach from a slightly different angle.

I'd type 2013 in Sheet2 Cell M2 then use some Code like this

Option Explicit
Private Sub Workbook_Open()
    With Sheets("Sheet2").Range("M2")
        If Not .Value = Year(Now) Then
            .Value = Year(Now)
        End If
    End With
End Sub

At the turn of a New Year the Code will change Sheet2 Cell M2 to the New Year and fire your Change Event Code.