Run Macro when columns change

[JamesB]

Hi, Can somebody please help me?
I was wanting to get some code that will run a macro when any cell from 2 specific columns changes.

Also I have 2 sheets to do this for, (Sheets are set up identically) is it possible to run the same macro on both sheets if one cell from the two sets of columns changes?

[foss20]

Hello,

There is a way but I will need more info from you to send you the exact code you will need. Please email me at foss20@msn.com


Mark Foss

[NateO]

Below is code you place in your "ThisWorkbook" module. Right-click on the Excel Icon the left of the Menu heading "File." Select "View Code." Paste the following code into the module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Index = 1 Then     ' 1'st sheet in workbook
    If Target.Column = 3 Or Target.Column = 4 Then  ' 3'rd and 4th colums are active
    Application.Run ("MyMacro") 'Change 'MyMacro to your Macro's name
    End If
End If
If Sh.Index = 2 Then     ' 2'st sheet in workbook
    If Target.Column = 3 Or Target.Column = 4 Then  ' 3'rd and 4th colums are active
    Application.Run ("MyMacro") 'Change 'MyMacro to your Macro's name
    End If
End If
End Sub

Change the sheet index numbers where appropriate and/or column numbers. If you prefer to use sheet name's, you can change the code to read like:

Sh.name = "mySheet" "Case [very] sensitive

If there are questions, post back. Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-30 15:36 ]

[JamesB]

Thanks for the code NateO,

It works a treat.

Cheers,

James.