Forum Discussion

xitcode's avatar
xitcode
Copper Contributor
Dec 18, 2020

How to move highlighted conditional formatted cells?

I want to move cells according to the cells that have been conditionally highlighted. I have selected Conditional Formatting so that I can identify cells which contains email addresses. The email addresses are in different column, and I want them in a specific column.

 

* For reference, I have attached the picture. The red highlighted cells contains the email addresses,

which I want to transfer to a specific column.

 

 

excel

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 18, 2020

    xitcode 

    Have cells been highlighted if they contain @ or did you use a more complicated rule?

    Anyway, you could create a macro that searches for the cells satisfying the conditional formatting rule (instead of looking at the cells' color) and moves them to the desired column. For example:

     

    Sub MoveMailAddresses()
    'Change these constants as needed
    Const ToCheck = "A:F"  ' Columns in which to look for mail address
    Const MailColumn = "M" ' Column to move mail address to (should not overlap with ToCheck)
    Dim Cell As Range
    With Range(ToCheck)
        Set Cell = .Find(What:="@", LookAt:=xlPart)
        If Not Cell Is Nothing Then
            Do
                Cell.Cut Destination:=Range(MailColumn & Cell.Row)
                Set Cell = .FindNext
            Loop Until Cell Is Nothing
        End If
    End With
End Sub

Resources