PDA

View Full Version : [SOLVED] Search for data highlighted in red return it and return the data in next two columns



estatefinds
07-29-2017, 09:20 AM
Hello, I need the current macro to be adjusted to search for a specific color 3( Red) in a range
and return the highlighted data and the data directly to the right of this data in the next two columns to the following columns AT1 AU1 AND AV1. this data will stay there, when i do a new search the result will be placed under previous result.
so I everytime a do a new search, a list will essentially be created.

Im attching file to show how it should look.

Any help On this is greatly appreciated!
Thank you very much in advance!!
Sincerely Dennis



Sub TestA_v2()
Dim Cll As Range
Dim n As Long
Application.ScreenUpdating = False
With ActiveSheet
For Each Cll In .Range("AN1:AN9548").SpecialCells(xlCellTypeConstants, 3)
If Cll.Interior.ColorIndex <> xlNone Then
n = n + 1
Cll.Copy .Cells(n, "AT")
End If
Next Cll
End With
Application.ScreenUpdating = True
End Sub

mdmackillop
07-29-2017, 03:48 PM
Sub TestA_v3()
Dim Cll As Range, c As Range
Dim n As Long
Application.ScreenUpdating = False
With ActiveSheet
For Each Cll In .Range("AN:AN").SpecialCells(xlCellTypeConstants, 3)
If Cll.Interior.ColorIndex = 3 Then
Set c = .Columns(46).Find(Cll)
If c Is Nothing Then _
Cll.Resize(, 3).Copy .Cells(Rows.Count, 46).End(xlUp)(2)
End If
Next Cll
End With
Application.ScreenUpdating = True
End Sub

estatefinds
07-29-2017, 06:47 PM
That worked awsome!!!!! Great job!!!!
Thank you very much!!!
Sincerely Dennis