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 <a href="mailto:foss20@msn.com">foss20@msn.com<a>


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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 15:36

 

[JamesB]

Thanks for the code NateO,

It works a treat.

Cheers,

James.

 

[wiseone]

Hello, I am trying to use this macro for my use-case which is similar, except that the macro I want to execute is row specific. If I change a cell in a column and then hit enter, the selected row goes to the next row, and then executes the macro on the wrong row based on the newly selected cell. I could update my macro to execute less 1 row, but I also have a button to execute this macro and if I execute it using the button instead, then it would target the incorrect row.

Any suggestions?

More specicially this is what I'm doing: My macro imports an exchange rate into an adjacent column, if a date is entered into the target cell.

Thanks in advance.