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

 

[lenze]

Hey Lenze,

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

Cheers!

K

Yes!! So, if they are different per sheet, you must either palce a macro in EACH sheet with specific instructions for Ranges, OR, modify the code above using Select Case to identify the sheet changed to set the Ranges.

lenze

 

[Imajika]

Ok, new problem. Apparently I have reach some kind of maximum in my ranges because adding more causes an error:


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

If Not Intersect(Target, Range("D2:D11,F2:F11,H2:H11,J2:J11,L2:L11,N2:N11,P2:P11,R2:R11,T2:T11,V2:V11,X2:X11,Z2:Z11,AB2:AB11,AD2:AD11,AF2:AF11,AH2:AH11,AJ2:AJ11,AL2:AL11,AN2:AN11,AP2:AP11,AR2:AR11,AT2:AT11,AV2:AV11,AX2:AX11,AZ2:AZ11,BB2:BB11,BD2:BD11,BF2:BF11,BH2:BH11,BJ2:BJ11,BL2:BL11")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = "X"
Application.EnableEvents = True
End If

End Sub


I have a few more ranges to add. How do I fix this?

K

 

[VoG]

Perhaps

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r1 As Range, r2 As Range, rng As Range
Set r1 = Range("D2:D11,F2:F11,H2:H11,J2:J11,L2:L11,N2:N11,P2:P11,R2:R11,T2:T11,V2:V11,X2:X11")
Set r2 = Range("Z2:Z11,AB2:AB11,AD2:AD11,AF2:AF11,AH2:AH11, AJ2:AJ11,AL2:AL11,AN2:AN11,AP2:AP11,AR2:AR11,AT2:AT11,AV2:AV11,AX2:AX11,AZ2:AZ11,BB2:BB11,BD2:BD11,BF2:BF11,BH2:BH11,BJ2 :BJ11,BL2:BL11")
Set rng = Union(r1, r2)
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = "X"
    Application.EnableEvents = True
End If
End Sub

 

[lenze]

Do you just want this to apply to every other Column, Rows 2-11?? If so, there's an easier way!!!

If Target.Row = 1 Then Exit Sub
If Target.Row > 11 Then Exit Sub
If Target.Column < 4 Then Exit Sub
If Target.Column Mod 2 = 0 Then Exit Sub
Target.Offset(0, 1) = "X"

lenze

 

[lenze]

My above code is set for ODD numbered columns!! I just noticed you are using EVEN numbered columns so Change

If Target.Column Mod 2 = 0 Then Exit Sub

to

If Target.Column Mod 2 = 1 Then Exit Sub

lenze

 

[Imajika]

Hey guys, this is great!

I understand what Peter has coded, mainly because this way I can see a clear beginning and end to my ranges. But I am also aware that it is much more cumbersome to write this way. I understand now what Lenze has coded and was able to define and end to the ranges by using the same logic.

Again, thank you very much! I will most likely have a few more questions before this project is done.

Cheers!

K

 

[Imajika]

Ok guys, here is another:

In regards to my original query, everything works fine when new data is entered within the targeted cells.

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


Now, how can I keep the same functionality if some of the target cells in question have a function that copies data from another cell? (ie B2=A2)

 

[VoG]

You would need to use the Calculate event - example

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("B1:B10")
    If c.Value > 1 Then c.Offset(, 1).Value = 1
Next c
End Sub

 

[Imajika]

Alright, I have yet another question:

I have a multitude of checkboxes related to specific cells. When the related cell has data, the checkbox activation should create a calculation in a few other cells, otherwise those target cells should remain empty.
Is this the correct way about it and is there any way I can simplify it?

Private Sub CheckBox1_Click()
' Checkbox rules for EQUIPMENT LIST row 2
If Range("B2").Value = True Then
Range("CF2").Formula = "=AE2-270"
Range("CH2").Formula = "=AE2-180"
Range("CJ2").Formula = "=AE2-90"
Range("CL2").Formula = "=AE2-30"
End If
If Range("AE2").Value = False Then
Range("CF2").Value = ""
Range("CH2").Value = ""
Range("CJ2").Value = ""
Range("CL2").Value = ""
End If
End Sub




Cheers!

K