Cell Change Event (VBA)

[TheRogue!]

I'll preface by saying that I know next-to-nothing about VBA.

I need to implement a Cell Change Event that if someone types anything in cell A1, cell C1 will be updated to say "banana" & if someone types anything in cell B1, cell C1 will be updated to say "strawberry". In turn, if after typing in B1, someone types in A1, C1 will change to "banana" again.

Short version: if someone types in A1 or B1, C1 changes, based upon the last change.

If any other cell is typed in, it does nothing.

Can anyone help me w/ code for this?

[nathansav]

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address(False, False)
    Case "A1": Range("C1").Value = "Bannanas"
    Case "B1": Range("C1").Value = "Strawberries"
    Case Else
End Select

End Sub

[nathansav]

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address(False, False)
    Case "A1": Range("C1").Value = "Bannanas"
    Case "B1": Range("C1").Value = "Strawberries"
    Case Else
End Select

End Sub

[TheRogue!]

Thank you SO much! That works great.

I may be pushing my luck here, but I just thought of another wrinkle that I overlooked:
Can I use named ranges in VBA? I tested your code & realized that I'm actually trying to apply it to a table.

So, ultimately, I'd be looking for it to anytime the was a change in ColumnA or ColumnB, it would be updating the cell in ColumnC, in the same row.

Something like:
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address(False, False)
Case "Table1[ColumnA]": Range("Table1[ColumnC]").Value = "Bannanas"
Case "Table1[ColumnB]": Range("Table1[ColumnC]").Value = "Strawberries"
Case Else
End Select

End Sub

Would that work? Or am I now asking too much?

[nathansav]

Something like this

Private blnRunning As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

Dim l As Excel.ListObject
Dim lc As ListColumn
Dim r As Integer

If Not Target.ListObject Is Nothing And Not blnRunning Then
    Set l = Target.ListObject

    With l
        Set lc = .ListColumns(Target.Column - (.HeaderRowRange.Cells(1).Column) + 1)
        r = Target.Row - .HeaderRowRange.Row
    End With
    
    If lc.Name = "Column1" Then
        blnRunning = True
        Range(l.Name & "[Column3]").Value = "Bannana"
    ElseIf lc.Name = "Column2" Then
        blnRunning = True
        Range(l.Name & "[Column3]").Value = "Strawberry"
    End If
    
    blnRunning = False
    
End If


End Sub