Identify Table Row Change
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Identify Table Row Change
Is it possible to identify the row number(s) of a table once a field in table changed?
Regards,
John
John
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identify Table Row Change
Can you provide an example of what you mean, and what form the output should have?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Identify Table Row Change
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identify Table Row Change
I'm afraid I don't understand what you want to accomplish, nor what output you'd want. Sorry!
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Identify Table Row Change
Hans,
As an example Account 2222's value in Feb changes from 21 to 50 thus the budget total reflects that change: 63 to 92. Changes to the table can be any row for the budget Jan-Mar as well as the forecast Jan-Mar.
Perhaps some logic that would put a flag to the right of the table on the same row that changed.
Budget change flag = 1, Forecast change flag = 1 basically True or False
If both changed the flag should be "11", only budget = "10", only forecast = "01"
If nothing changed then there is no flag.
As an example Account 2222's value in Feb changes from 21 to 50 thus the budget total reflects that change: 63 to 92. Changes to the table can be any row for the budget Jan-Mar as well as the forecast Jan-Mar.
Perhaps some logic that would put a flag to the right of the table on the same row that changed.
Budget change flag = 1, Forecast change flag = 1 basically True or False
If both changed the flag should be "11", only budget = "10", only forecast = "01"
If nothing changed then there is no flag.
Regards,
John
John
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identify Table Row Change
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim m As Long
Dim c As Range
Dim d As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
m = Range("B" & Rows.Count).End(xlUp).Row
If Not Intersect(Range("D5:F" & m), Target) Is Nothing Then
For Each c In Intersect(Range("D5:F" & m), Target).Rows
Set d = Range("L" & c.Row)
Select Case d.Value
Case "01", "11"
d.Value = "11"
Case Else
d.Value = "10"
End Select
Next c
End If
If Not Intersect(Range("H5:J" & m), Target) Is Nothing Then
For Each c In Intersect(Range("H5:J" & m), Target).Rows
Set d = Range("L" & c.Row)
Select Case d.Value
Case "10", "11"
d.Value = "11"
Case Else
d.Value = "01"
End Select
Next c
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California