Running a macro if more than one cell changes?

Hi all,

I have a sheet, and need to run several instances of "If a cell changes value, THEN run a command"

I can do a single one fine :

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("S2")) Is Nothing Then
Exit Sub
 Else
Call Worksheet_Activate
End If

but i also need to put in something like:

If the value of C2 = "Clear Sheets" then ....
If the value of C2 = "Save" then...
IF the value of C2 = "Save and Email" then..

how would i make this happen?

Thank you all so much

OK so lots of trial and error later, i have managed to do it..

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cell As Object

      For Each Cell In Range("D2:D2")
                    If Cell.Value = "Clear Sheet" Then
                        If MsgBox("Are you sure you want to clear all data?", vbYesNo, "Clear data...") = vbYes Then
                            Sheets("Hours").unprotect Password = "temp"
                            Worksheets("Hours").Range("D7:O59").ClearContents
                            Worksheets("Hours").Range("D2").Value = "Options..."
                            Sheets("Hours").Protect Password = "temp"
                            Exit Sub
                            Else
                            End If
                        Worksheets("Hours").Range("D2").Value = "Options…"
                        Else
                    If Cell.Value = "Save" Then
                        MsgBox "SAVING"
                        Else
                    If Cell.Value = "Save & Email" Then
                        MsgBox "SAVING AND EMAILING"
                        Else
                    If Cell.Value = "Print" Then
                        MsgBox "PRINTING"
                        Else
   
   
                    End If
                    End If
                    End If
                    End If
        Next Cell
        
    End Sub

BUTTTTTTTT


When i ask it to change cell D2 back to "Options..." it sees the cell has changed, and just runs the macro again!

Anyway of bypassing it?

Your macro actions the macro based on D2 contents every time any entry on your sheet is changed.

I not sure if that is what you are after or just to run when the contents of D2 is changed
If you require it to run on every cell change then replace
For Each Cell In Target
If Not Intersect(Target, Range("d2")) Is Nothing Then
with
For Each Cell In Range("D2:D2")

I have made some changes to the macro so it runs when D2 is changed.
I have also added some modifications to your If statements.

Re your origenal question add
Application.EnableEvents = False
at the start of your code.
You must also make sure that the macro runs
Application.EnableEvents = True
Otherwise the macro will stop working and so will any other macro's that use trigger events to run them.


The macro runs quicker if you declare Cell as a Range instead of declaring Cell as Object
Have a look at
http://www.cpearson.com/excel/variables.htm
Especially the Don't Use Object Type Variables section

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cell As Range
   
   For Each Cell In Target
      If Not Intersect(Target, Range("d2")) Is Nothing Then
         Application.EnableEvents = False
         If Cell.Value = "Clear Sheet" Then
            If MsgBox("Are you sure you want to clear all data?", vbYesNo, "Clear data...") = vbYes Then
               Sheets("Hours").Unprotect Password = "temp"
               Worksheets("Hours").Range("D7:O59").ClearContents
               Worksheets("Hours").Range("D2").Value = "Options..."
               Sheets("Hours").Protect Password = "temp"
               Exit For
            End If
            Worksheets("Hours").Range("D2").Value = "Options…"
         Else
            If Cell.Value = "Save" Then
                MsgBox "SAVING"
            ElseIf Cell.Value = "Save & Email" Then
               MsgBox "SAVING AND EMAILING"
            ElseIf Cell.Value = "Print" Then
               MsgBox "PRINTING"
            End If
         End If
      End If
   Next Cell
   Application.EnableEvents = True
End Sub