Colorindex referencing
 

I am looping thru thousands of rows copying the format from one to
another based on a certain condition.

Here is what I have now:
Cells(i, 8).Copy
Cells(j, 18).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


How can I do this faster as it takes a lot of time to run.
How can I access the colorindex of a cell and use it to fill the target
cell?

Thanks
Steve

Colorindex referencing
 

Range(A1:A1000).PasteSpecial Paste:=xlPasteFormats

--
Kind regards,

Niek Otten

"gtslabs" wrote in message
ups.com...
I am looping thru thousands of rows copying the format from one to
another based on a certain condition.

Here is what I have now:
Cells(i, 8).Copy
Cells(j, 18).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


How can I do this faster as it takes a lot of time to run.
How can I access the colorindex of a cell and use it to fill the target
cell?

Thanks
Steve

Colorindex referencing
 

But maybe that is no good as it copies the formats of all those cells
irrespective of the condition, whatever that condition is.

RBS

"Niek Otten" wrote in message
...
Range(A1:A1000).PasteSpecial Paste:=xlPasteFormats

--
Kind regards,

Niek Otten

"gtslabs" wrote in message
ups.com...
I am looping thru thousands of rows copying the format from one to
another based on a certain condition.

Here is what I have now:
Cells(i, 8).Copy
Cells(j, 18).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


How can I do this faster as it takes a lot of time to run.
How can I access the colorindex of a cell and use it to fill the target
cell?

Thanks
Steve

Colorindex referencing
 

True. It wasn't in the example, but if for each cell a condition has to be
tested, it does indeed get tedious.

--
Kind regards,

Niek Otten

"RB Smissaert" wrote in message
...
But maybe that is no good as it copies the formats of all those cells
irrespective of the condition, whatever that condition is.

RBS

"Niek Otten" wrote in message
...
Range(A1:A1000).PasteSpecial Paste:=xlPasteFormats

--
Kind regards,

Niek Otten

"gtslabs" wrote in message
ups.com...
I am looping thru thousands of rows copying the format from one to
another based on a certain condition.

Here is what I have now:
Cells(i, 8).Copy
Cells(j, 18).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


How can I do this faster as it takes a lot of time to run.
How can I access the colorindex of a cell and use it to fill the target
cell?

Thanks
Steve

Colorindex referencing
 

This is what I have but it still takes some time to run

showcolorindex = Cells(j, 8).Interior.ColorIndex
Cells(j, 18).Interior.ColorIndex = showcolorindex

Colorindex referencing
 

This is what I have but it still takes some time to run

showcolorindex = Cells(j, 8).Interior.ColorIndex
Cells(j, 18).Interior.ColorIndex = showcolorindex

Colorindex referencing
 

This is what I have but it still takes some time to run

showcolorindex = Cells(j, 8).Interior.ColorIndex
Cells(j, 18).Interior.ColorIndex = showcolorindex

Colorindex referencing
 

Hello gtslabs,

You can speed the code up by copying the ColorIndex into an array
first. This seems counterintuitive to just making one to one
assignments. This code runs in 4 seconds on my machine. You can easily
adapt it to your needs.


Code:
--------------------

Sub CopyColors()

Dim ColorArray(10000) As Long
Dim R As Long

R = 0
For Each C In Range("H1:H10000")
R = R + 1
ColorArray(R) = C.Interior.ColorIndex
Next C

R = 0
For Each C In Range("R1:R10000")
R = R + 1
C.Interior.ColorIndex = ColorArray(R)
Next C

End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=493171

Colorindex referencing
 

Or maybe what you can do is copy all the values of the range that holds the
condition to be tested, use conditional formatting on the second range and
then make the values
disappear by setting the font to white.

RBS

"gtslabs" wrote in message
oups.com...
This is what I have but it still takes some time to run

showcolorindex = Cells(j, 8).Interior.ColorIndex
Cells(j, 18).Interior.ColorIndex = showcolorindex