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

[Imajika]

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

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

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

 

[Imajika]

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

 

[VoG]

Try

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

 

[Imajika]

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

 

[Imajika]

Ok, follow-up question. What if I have multiple ranges, how would I modify this code?

K

 

[VoG]

For example

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

 

[lenze]

FYI: If you place Peter's code in the ThisWorkBook module as

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

 

[Imajika]

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

 

[Imajika]

Hey Lenze,

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

Cheers!

K