swap cells in same column based on multiple criteria

[RedOctoberKnight]

What if i wanted to switch the contents of two cells that are in the same column? But have it switch with a specified cell based on a cell value.

For example,

A	B	C	D
Jim	Cat	Bob	SWITCH
Dan	Dog
Bob	Bird
Greg	Cat

So basically, if I put Bob in column "C" and put "SWITCH" in column "D", it would switch the contents of column B between Jim and Bob

A	B	C	D
Jim	Bird	Bob	SWITCH
Dan	Dog
Bob	Cat
Greg	Cat

Hopefully that makes sense.

Thank you for all your help.

 

[Legacy 143009]

Hi,

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("C")) Is Nothing Or Not Intersect(Target, Columns("D")) Is Nothing Then
      If Cells(Target.Row, "C").Value <> "" And Cells(Target.Row, "D").Value = "SWITCH" Then
        Dim temp As Variant
        With Application
        .EnableEvents = False
        If Not IsError(.Match(Cells(Target.Row, "C").Value, Columns("A"), 0)) Then
          temp = Cells(.Match(Cells(Target.Row, "C").Value, Columns("A"), 0), "B").Value
          Cells(.Match(Cells(Target.Row, "C"), Columns("A"), 0), "B").Value = Cells(Target.Row, "B").Value
          Cells(Target.Row, "B").Value = temp
        End If
        .EnableEvents = True
        End With
      End If
  End If
End Sub

 

[RedOctoberKnight]

Hi,

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("C")) Is Nothing Or Not Intersect(Target, Columns("D")) Is Nothing Then
      If Cells(Target.Row, "C").Value <> "" And Cells(Target.Row, "D").Value = "SWITCH" Then
        Dim temp As Variant
        With Application
        .EnableEvents = False
        If Not IsError(.Match(Cells(Target.Row, "C").Value, Columns("A"), 0)) Then
          temp = Cells(.Match(Cells(Target.Row, "C").Value, Columns("A"), 0), "B").Value
          Cells(.Match(Cells(Target.Row, "C"), Columns("A"), 0), "B").Value = Cells(Target.Row, "B").Value
          Cells(Target.Row, "B").Value = temp
        End If
        .EnableEvents = True
        End With
      End If
  End If
End Sub

Perfect! Thank you so much!

 

[Legacy 143009]

You are welcome Thanks for the feedback