VBA Update a sub set of records

Al Del

Board Regular
Joined
Jan 9, 2007
Messages
112
I am using the following macro to place a "w" in column M (9) of the target record of a worksheet when colmun D gets the entry "Filled".

Code:
    Dim lastrow As Integer
    lastrow = Range("D" & Rows.Count).End(xlUp).row
    
    'If Status is "Filled" place a "w" in Results
    'Range("D2:D" & lastrow) Is the Target it defines the column D as the target area
    If Not Intersect(Target, Range("D2:D" & lastrow)) Is Nothing Then
        If Target <> "Filled" Then
            Target.Offset(, 9) = vbNullString
            Else: Target.Offset(, 9) = "w"
        End If
     End If

I would like to enter a "w" into col M of all records, that have the same entry in column K of the target row of the list, when anyone of these records gets the entry "Filled" put in col D of one of these records.
 
I've been trying to debug this code, same code as above, it turns out it doesn't seem to be the code, but the code is not being triggered when I change column D. I put a stop at the beginning of the procedure and it never gets triggered. The code is in Sheet19, any ideas on what the problem might be?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Stop
If Target.Cells.Count <> 1 Then Exit Sub
Application.EnableEvents = False

    If Not Intersect(Target, Range("D:D")) Is Nothing Then

        If LCase(Target.Value) = "filled" Then
            Range("K:K").AutoFilter 1, Target.Offset(, 7).Value
            Range("K2", Cells(Rows.Count, "K").End(xlUp)).Offset(, 2).SpecialCells(xlCellTypeVisible).Value = "w"
            AutoFilterMode = False
        End If
    End If
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top