If statement with macro

B

bbot23

New Member
Joined
Oct 29, 2010
Messages
37
I google'd the code to run a macro depending on a met condition, but I can't seem to make it call the macro. Can anyone help? Here's the code that I got:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AG6")) Is Nothing Then
Exit Sub
End If

If Range("AG6") = 1 Then
SomeMacro
End If

End Sub

Sub SomeMacro()
MsgBox ("Hi!")
End Sub

If I change AG6 to 1, nothing happens. Help?
 
Sort by date Sort by votes

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
bbot23 said:
Aye, the best would be if ONLY the result changes in the cell with the formula.
Click to expand...

All you need is a mechanism for detecting when only AG6 changes. For this you'll need to spare a cell somewhere on your worksheet to record the previous value of AG6 so that when the sheet recalculates, it can check whether AG6 was changed. If we use AZ1 for this purpose:-
Code: 
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Private Sub Worksheet_Calculate()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  If Range("AZ1").Value <> Range("AG6").Value Then [COLOR=green]' AG6 has changed
[/COLOR]    Range("AZ1") = Range("AG6").Value              [COLOR=green]' store its new value in AZ1
[/COLOR]    If Range("AG6") = 1 Then SomeMacro
  End If
[/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Sub SomeMacro()
MsgBox ("Hi!")
End Sub
[/FONT]
If AZ1 isn't convenient, you can use any other cell you want.
 
Upvote 0
Ruddles,

Well done! This is a good and practical solution.

I think that using the Precedents property we can achieve a solution too.

Something like this.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AG6").Precedents) Is Nothing Then
        If Range("AG6") = 1 Then
            SomeMacro
        End If
    End If
    
End Sub
 
Sub SomeMacro()
   MsgBox ("Hi!")
End Sub

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA and date formatting
Replies
8
Views
189
Joe4
Joe4
D
  • Question Question
VBA to lock Cell after Data Entry but still Allow Sort & Filter ?
Replies
3
Views
237
Jeffrey Mahoney
Jeffrey Mahoney
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
117
welshraz
welshraz
H
  • Solved
UserForm
Replies
8
Views
131
mumps
mumps
E
  • Solved
Run Macro if Cell Contains...
2 3
Replies
23
Views
433
Skyybot
Skyybot

Forum statistics

Threads
1,224,609
Messages
6,179,879
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top