Specific question - How to create a macro that identifies data change in cell

I

Imajika

New Member
Joined
Feb 8, 2010
Messages
17
Hello all,

I am new to the whole world of VB but learning things pretty quickly. I need help although with the following situation:

for example, I have data (a date) in cell A1 and I would like a macro to change the data in cell B1 when the data in cell A1 has changed. i.e if the date has been manually changed in A1, I would like the data in cell B1 to change automatically (from 0 to 1).

I have this scenario in multiple cells across multiple sheets, so I plan on copying and pasting this macro where necessary.

Is this possible?
Cheers!


K
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Try this: right click the sheet tab, select View Code and paste in

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Range("B1").Value = 1
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Beautiful, it works. Now, what if I wanted that to work for a range, say changes in A1 to A10 would change B1 to B10 (on a one to one basis)?
How would I write this?

Cheers and thank you!

K
 
Upvote 0
Try

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = 1
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Sir,

You are a scholar and a gentleman. Works like a charm! Looks like I have quite a ways to go before I get comfortable with this language. You have seriously helped me out with my project and for this I am grateful. Hopefully I can call upon your knowledge again in the future.

Cheers!

Ken
 
Upvote 0
For example

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10,C1:C20,E3:E17")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = 1
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
FYI: If you place Peter's code in the ThisWorkBook module as
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'the code
End Sub
it will work on all sheets in the WB!!
lenze
 
Upvote 0
I see what I was doing wrong:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2:D11","f2:f11","h2:h11")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = "X"
Application.EnableEvents = True
End If
End Sub

Thanks for the help, again!

K
 
Upvote 0
Hey Lenze,

Would this mean that the identified ranges would apply equally to all worksheets in the WB?

Cheers!

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

Similar threads

jbillh
  • Question Question
How to add up quantities that are associated ONLY with an "Identifier word" like a SKU or word across 2 or 3 sheets in the same workbook?
2
Replies
15
Views
249
Peter_SSs
Peter_SSs
S
  • Question Question
Copying value when cell value changes
Replies
8
Views
256
MARK858
MARK858
S
  • Question Question
Lock Data Results in cell's while changing the data that populated it?
Replies
3
Views
193
myall_blues
myall_blues
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B
T
  • Solved
Move cursor to a specific cell after data has been entered
Replies
4
Views
215
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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