How to move highlighted conditional formatted cells?

%3CLINGO-SUB%20id%3D%22lingo-sub-2002156%22%20slang%3D%22en-US%22%3EHow%20to%20move%20highlighted%20conditional%20formatted%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2002156%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20move%20cells%20according%20to%20the%20cells%20that%20have%20been%20conditionally%20highlighted.%20I%20have%20selected%20Conditional%20Formatting%20so%20that%20I%20can%20identify%20cells%20which%20contains%20email%20addresses.%20The%20email%20addresses%20are%20in%20different%20column%2C%20and%20I%20want%20them%20in%20a%20specific%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20For%20reference%2C%20I%20have%20attached%20the%20picture.%20The%20red%20highlighted%20cells%20contains%20the%20email%20addresses%2C%3C%2FP%3E%3CP%3Ewhich%20I%20want%20to%20transfer%20to%20a%20specific%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22conditional%20formatting.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241615i75B72D28C4C0386C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22conditional%20formatting.png%22%20alt%3D%22conditional%20formatting.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2002156%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2002404%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20move%20highlighted%20conditional%20formatted%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2002404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776693%22%20target%3D%22_blank%22%3E%40xitcode%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20cells%20been%20highlighted%20if%20they%20contain%20%40%20or%20did%20you%20use%20a%20more%20complicated%20rule%3F%3C%2FP%3E%0A%3CP%3EAnyway%2C%20you%20could%20create%20a%20macro%20that%20searches%20for%20the%20cells%20satisfying%20the%20conditional%20formatting%20rule%20(instead%20of%20looking%20at%20the%20cells'%20color)%20and%20moves%20them%20to%20the%20desired%20column.%20For%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MoveMailAddresses()%0A%20%20%20%20'Change%20these%20constants%20as%20needed%0A%20%20%20%20Const%20ToCheck%20%3D%20%22A%3AF%22%20%20'%20Columns%20in%20which%20to%20look%20for%20mail%20address%0A%20%20%20%20Const%20MailColumn%20%3D%20%22M%22%20'%20Column%20to%20move%20mail%20address%20to%20(should%20not%20overlap%20with%20ToCheck)%0A%20%20%20%20Dim%20Cell%20As%20Range%0A%20%20%20%20With%20Range(ToCheck)%0A%20%20%20%20%20%20%20%20Set%20Cell%20%3D%20.Find(What%3A%3D%22%40%22%2C%20LookAt%3A%3DxlPart)%0A%20%20%20%20%20%20%20%20If%20Not%20Cell%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cell.Cut%20Destination%3A%3DRange(MailColumn%20%26amp%3B%20Cell.Row)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20Cell%20%3D%20.FindNext%0A%20%20%20%20%20%20%20%20%20%20%20%20Loop%20Until%20Cell%20Is%20Nothing%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

 

conditional formatting.png

1 Reply

@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