• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste cell with specific colour

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!!



Well-Known Member
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