Copy and paste cell with specific colour

[shashwat Agarwal]

I have data with cells filled with some colours. I want to copy and paste the only cell that have a specific colour.

I have attached a sample sheet where I want to copy cells with Colour Orange in Sheet2.

Please help!!

 

[AlanSidman]

You have no cells with the color orange in your sample, however, if you wish to copy cells with the color red then the following code will work. Change the color index to whatever color you wish.

Look at this link for color index: http://dmcritchie.mvps.org/excel/colors.htm

Option Explicit

Sub copycolor()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim c As Range, rng As Range, lr As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set rng = s1.Range("C3").CurrentRegion
    For Each c In rng
        If c.Interior.ColorIndex = 3 Then    'Red color index
            lr = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            c.Copy s2.Range("A" & lr)
        End If
    Next c

End Sub

 

[shashwat Agarwal]

Thank you so much AlanSidman!!