Cells not updating with Worksheet_Change(ByVal Target As Excel.Range)

[LlebKcir]

Thank you everyone in advance for the help. I am still learning basic VBA so this one is a bit of a stretch...

For all cells in column A that are updated (be that manually entered, or updated via click and drag, other) I want a specific output in column B. I have the if statements working as desired when data is manually entered into column A, but any other type of manipulation including mass select and delete fail to update column B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row


            If Range("a" & ThisRow).Value Like "*foo1*" Or Range("a" & ThisRow).Value Like "*foo2*" Then
                Range("b" & ThisRow).Value = "OUT1"
            Else
                If Range("a" & ThisRow).Value Like "*foo3*" Or Range("a" & ThisRow).Value Like "*foo4*" Or Range("a" & ThisRow).Value Like "*foo5*" Then
                    Range("b" & ThisRow).Value = "OUT2"
            Else
                If Range("a" & ThisRow).Value Like "*foo6*" Or Range("a" & ThisRow).Value Like "*foo7*" Or Range("a" & ThisRow).Value Like "*foo8*" Or Range("a" & ThisRow).Value Like "*f0016*" Then
                    Range("b" & ThisRow).Value = "OUT3"
            Else
                If Range("a" & ThisRow).Value Like "*foo9*" Or Range("a" & ThisRow).Value Like "*foo10*" Or Range("a" & ThisRow).Value Like "*foo11*" Or Range("a" & ThisRow).Value Like "*foo17*" Then
                    Range("b" & ThisRow).Value = "OUT4"
            Else
                If Range("a" & ThisRow).Value Like "*foo12*" Then
                    Range("b" & ThisRow).Value = "OUT5"
            Else
                If Range("a" & ThisRow).Value Like "*foo13*" Or Range("a" & ThisRow).Value Like "*foo14*" Or Range("a" & ThisRow).Value Like "*foo15*" Then
                    Range("b" & ThisRow).Value = "OUT6"
            Else
                If IsEmpty(Range("a" & ThisRow).Value) Then
                    Range("b" & ThisRow).Value = ""
            End If
            End If
            End If
            End If
            End If
            End If
            End If


    End If


End Sub

so rather simple set of code, but what am i missing to enable the click/drag or double click on a cell in column A to update all of the equal number of cells in column B?

Thank you again in advance.

 

[MARK858]

roww is just the row number of the cell being looped i.e. rng in the code.

Btw, you don't mark posts as Solved on this forum, you just say thank you which you have already done

 

[LlebKcir]

roww is just the row number of the cell being looped i.e. rng in the code.

Btw, you don't mark posts as Solved on this forum, you just say thank you which you have already done

Got ya on the cell being looped. That explains why that works. The little digging I found that Excel only marks the initially selected cell as "active" and thus the vba code was failing to update the non-active cells for desired output.

Thank you for the info. It is always a great way to start the day by learning something new.

 

[MARK858]

You're welcome and as you have probably noted by what Fluff posted you rarely need to Select/Activate anything to work with them in VBA.

 

[LlebKcir]

You're welcome and as you have probably noted by what Fluff posted you rarely need to Select/Activate anything to work with them in VBA.

Sorry I was not clear. The initial code I posted functioned with the following issues:

column B, only populated when column A was manually edited.

ex: manually type any desired filter for the LIKE() function, hit enter and column B would populated desired OUT.
copy/paste the cell from column A down and only 1 cell in column B would populate. The same held true for deleting cells in column A.

The updated code functions no matter how column A is edited.