Dec 18 2020 10:36 AM
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.
Dec 18 2020 12:09 PM
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