I have been struggling to get a macro together to perform some tricky
coloring functions. With the kind help of a number of people in the 'Excel
Worksheet Forum' I have got close to achieving something which works but need
help to get the macro to run. Here is where I have got to:


The following is an example macro, kindly 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