How select/copy text in red color from array?

mellangerjulien · September 14th, 2009, 11:35 AM

Hi,
I m looking for a code in order to find automatically all cells containing red text in the array (A2:E1000) then copy this selection in the sheet2.
I think this code is not complicated but I just started and need some help.
Thanks very much,
Julien

SheriV · September 14th, 2009, 12:32 PM

Here is a simple method:

Code:

Dim vRng, vCnt
Sub DoEm()
    vCnt = 1
    vR = 2
    vR1 = 2
    vRng = "A"
    While vCnt < 6
        While Range("vRng" & vR).Value <> ""
            If Range("vRng" & vR).Font.ColorIndex = 3 Then
                Range("vRng" & vR).Copy
                Sheets("Sheet2").Range("vRng" & vR1).PasteSpecial xlValues
                vR1 = vR1 + 1
            End If
            vR = vR + 1
        Wend
        vCnt = vCnt + 1
        SetColCase
        vR = 2
        vR1 = 2
    Wend
End Sub

Sub SetColCase()
        Select Case vCnt
        Case "2": vRng= "B"
        Case "3": vRng= "C"
        Case "4": vRng= "D"
        Case "5": vRng= "E"
      End Select
End Sub

Copy this code into your VBA Module and step through it and you'll get how it works. Someone may have better suggestion as not alot of folks use this type of method but for me, it's easy to understand and easy to learn on the fly. Hope this helps you.