Getting a macro to run automatically when a cell value changes

**sschluet** · 04-16-2013, 08:34 PM

I have some code I would like to execute automatically any time the value in C1 is changed:

Please Login or Register  to view this content.

I've tried adding

Please Login or Register  to view this content.

but no matter how many examples I've looked at I still seem to be missing something because it never runs automatically. What am I doing wrong here?

**davesexcel** · 04-16-2013, 08:50 PM

Please Login or Register  to view this content.

Possibly

Please Login or Register  to view this content.

The code belongs in the worksheet module

**seatejo** · 04-16-2013, 09:54 PM

may be try this one. put this code in the sheet you want the change to happen

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$C$1" Then
Application.Run "your code"

End If
End Sub

**sschluet** · 04-17-2013, 07:44 AM

Thanks for the suggestions. I tried the first approach and while the code runs it still forces me to run it manually.

For the second suggestion I get an error when I run it (and yes it also keeps me running it manually) Compile error: expected function or variable.

Any other ideas?

**davesexcel** · 04-17-2013, 08:14 AM

Originally Posted by sschluet

Thanks for the suggestions. I tried the first approach and while the code runs it still forces me to run it manually.........

Read the last line in my post.

The code belongs in the worksheet module

Right click the sheet tab and select view code, you are now in the worksheet module.

Worksheet Events

**sschluet** · 04-17-2013, 12:31 PM

Ok, I moved the code to the worksheet module, changed it so it's not using "sh" and added what you have there:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then
If Target = "" Then
Range("I2:I6").ClearContents
Else: common2
End If
End If
End Sub

Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
'Set sh = Sheets(6) 'Edit sheet name

If Range("C1").Value = "" Then
Range("I2:I6").ClearContents
Else

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & lr)
For Each C In rng
Set Brng = Range("B:B").Find(C.Value, LookIn:=xlValues)
Set Crng = Range("C:C").Find(C.Value, LookIn:=xlValues)
If Not Brng Is Nothing And Not Crng Is Nothing Then
Cells(Rows.Count, 9).End(xlUp)(2) = C.Value
End If
Set Brng = Nothing
Set Crng = Nothing
Next

End If

End Sub

It still doesn't run unless I do things manually. I'm still open for ideas.

**davesexcel** · 04-17-2013, 07:28 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**sschluet** · 04-18-2013, 08:41 AM

I'm guessing what you want me to do is this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It still doesn't run unless I do things manually. I'm still open for ideas.

**davesexcel** · 04-18-2013, 07:20 PM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic