Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colorindex | Excel Discussion (Misc queries) | |||
ColorIndex | Excel Programming | |||
ColorIndex | Excel Programming | |||
ColorIndex | Excel Programming |