try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin