hi bj,

almost there ..

in your 2nd example, what you have is very close except there is a problem
with the target location ..

The target location is supposed to be identified by the contents of whatever
is in column1, row n of sheet1, so ..

Lets say we have the following on sheet1 ..

Row1, ColumnA, value = C1
Row1, ColumnC, color = <green

Row2, ColumnA, value = B4
Row2, ColumnC, color = <blue

Row3, ColumnA, value = A7
Row3, ColumnC, color = <red

(the actual number of rows to be processed should be controlled by the loop
variable 'r')

After running the macro I would like to see the following appear on sheet2 ..

Row1, ColumnC, cell colored green
Row4, ColumnB, cell colored blue
Row7, ColumnA, cell colored red

sorry for not stating my requirement clearly, I hope this makes sense,

Kevin


"bj" wrote:

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

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