Identify Table Row Change

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Identify Table Row Change

Post by jstevens »

Is it possible to identify the row number(s) of a table once a field in table changed?
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Identify Table Row Change

Post by HansV »

Can you provide an example of what you mean, and what form the output should have?
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Identify Table Row Change

Post by jstevens »

Hans,

Here is a sample file.
EL_table_row.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Identify Table Row Change

Post by HansV »

I'm afraid I don't understand what you want to accomplish, nor what output you'd want. Sorry!
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Identify Table Row Change

Post by jstevens »

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.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Identify Table Row Change

Post by HansV »

Right-click the sheet tab.
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

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Identify Table Row Change

Post by jstevens »

Hans,

Exactly what I was looking for.

Thank you!
Regards,
John