[EXCEL] Help making multiple values in one column change based on another column

[jaredmason]

I have a table somewhat like the following:

APPLE  1
BANANA 0
BANANA 0
ORANGE 1
APPLE  1
BANANA 0

Whenever I make a change in column B I would like all other cells in column B with the same type in column A to also change. So if I changed B1 to '0' then B5 would also change to '0'. If I changed B2 to '1' then B3 and B6 would also change to '1'.

Unfortunately, I don't really have the first clue where to start with this. I am thinking I will use Worksheet_Change, but that's as far as I've come.

Any help very much appreciated

[Laterus]

Use find and replace.

[vbfbryce]

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRow As Long
    Dim chgRow As Long
    Dim chgToVal As Integer     'some number that was changed TO
    Dim chgToKey As String      'apple
    Dim i As Integer
    
    If Target.Column = 2 Then
        Application.EnableEvents = False    'don't get stuck in a change event loop
        chgRow = Target.Row
        chgToVal = Target.Value
        chgToKey = Target.Offset(0, -1).Text
        lastRow = Range("a" & Rows.Count).End(xlUp).Row
        
        For i = 1 To lastRow
            If i <> chgRow Then
                If Range("a" & i).Text = chgToKey Then
                    Range("b" & i).Value = chgToVal
                End If
            Else
                'skip it
            End If
        Next i
    End If
    Application.EnableEvents = True
End Sub

Your data must be in columns A and B, starting in row 1. Otherwise, change the code accordingly.