VBA - Select only table

[sparkytech]

I have the code below (created by others) that highlights changes to cells. It currently does this for the entire sheet. How can I limit this to the table?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
Dim i As Double
i = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If Target.Row > i Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 30)).Interior.ColorIndex = 46
ElseIf Target.Row <= i Then
Target.Interior.ColorIndex = 19
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Georgiboy]

Maybe something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.ListObjects(1).DataBodyRange) Is Nothing Then
        Target.Interior.ColorIndex = 19
    End If
End Sub

 

[sparkytech]

Maybe something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.ListObjects(1).DataBodyRange) Is Nothing Then
        Target.Interior.ColorIndex = 19
    End If
End Sub

Thanks for the code! Stupid question, I know... how do I integrate this into my existing code?

 

[Georgiboy]

From reading through your original code it looked like it was making the amended cells turn a different colour.

The code provided will colour anything inside the table that has been amnded. If that is all it needs to do then my code would replace yours.

Are there any other requirements of the code other than to colour changed cells inside the table?

 

[Georgiboy]

I assumed the other parts to the code were being applied to anything outside of the table.

 

[sparkytech]

I assumed the other parts to the code were being applied to anything outside of the table.

As I mentioned, I didn't create the code, and haven't reverse engineered it yet (still learning VBA).

I need the code to only apply to the table, and actually limit it to columns B:W. The only function I need is to highlight cells that are changed. With that being said, I couldn't get your code to work. I put it in the sheet, replacing my original code. Is that the correct place to put this? Thanks again!

 

[Joe4]

What is the name of your table?
What columns are your table in?
Where exactly is the "other" data you want to exclude located?

 

[sparkytech]

What is the name of your table?
What columns are your table in?
Where exactly is the "other" data you want to exclude located?

Name of table is Master_Table. Table is in columns A:AB (these cells need the "change" highlight, need to exclude columns A, and X:AB.

 

[sparkytech]

Name of table is Master_Table. Table is in columns A:AB (these cells need the "change" highlight, need to exclude columns A, and X:AB.

Correction... table is A:AB, only columns B:W need highlights. Columns A and X:AB do not need highlights.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("Master_Table"))
    
'   Exit if no updates to table
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    For Each cell In rng
'       See if update in columns B:W
        If cell.Column >= 2 And cell.Column <= 23 Then
            cell.Interior.ColorIndex = 19
        End If
    Next cell

End Sub