Matching Color in two Columns excel 2007

[fari1]

hi experts,
i'm wondering if this can be done with vba in excel 2007. i've two columns in my sheet1. in column A i've few cells(say about 10 or so) that are colored red and then green (again a few cells) and blue and so on. For every red colored cells from column A, there's a cell in column B, that matches the color with them, for every blue colored cells in column A, there's again a blue colored cell in column B.

what i want is to get a code, that matches all the cells of same color from Column A with the same colored cell of column B and copy that cell from column B to sheet2 Column C in the same colored cell.
i hope i didn't messed up.
thanking u as always

 

[machopicho]

Something like this?

Sub Macro1()
n = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To n
If Range("A" & r).Interior.ColorIndex = Range("B" & r).Interior.ColorIndex Then
Range("B" & r).Copy Destination:=Range("C" & r)
End If
Next
End Sub

 

[fari1]

hi, thanks for the response, this code is copying whatever is in column B and pasting it in C column of the same sheet, apart from no color matching, its not bringing in the matched data in sheet 3 in respective colored column Cell.

 

[machopicho]

This?

Sub Macro1() n = Range("A" & Rows.Count).End(xlUp).Row
nr = Range("B" & Rows.Count).End(xlUp).Row
For r = 1 To n
For rw = 1 to nr
 If Range("A" & r).Interior.ColorIndex = Range("B" & rw).Interior.ColorIndex Then 
Range("B" & rw).Copy Destination:=Range("C" & rw) 
End If
Next rw,r 
End Sub

To copy other sheet change:
Destination:=Range("C" & rw) with Destination:=Sheets("NAME YOUR SHEET")Range("C" & rw)

 

[fari1]

its giving error, end if without block if

 

[machopicho]

To me work!

Sub Macro1()
n = Range("A" & Rows.Count).End(xlUp).Row
nr = Range("B" & Rows.Count).End(xlUp).Row
For r = 1 To n
For rw = 1 To nr
 If Range("A" & r).Interior.ColorIndex = Range("B" & rw).Interior.ColorIndex Then
Range("B" & rw).Copy Destination:=Range("C" & rw)
End If
Next rw, r
End Sub

Attention: Cells in column A e B have to filled

 

[fari1]

i think, i didn't made it clear perhaps.
my requirement was to match colors in two columns and put that matched figure from column B to column A. but its just copying all the data to sheet2. its not matching colors and then copying