Colorindex referencing

[gtslabs]

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

[Niek Otten]

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

--
Kind regards,

Niek Otten

"gtslabs" <[email protected]> wrote in message
news:[email protected]...
>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
>

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> Range(A1:A1000).PasteSpecial Paste:=xlPasteFormats
>
> --
> Kind regards,
>
> Niek Otten
>
> "gtslabs" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

[gtslabs]

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

[gtslabs]

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

[Niek Otten]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Range(A1:A1000).PasteSpecial Paste:=xlPasteFormats
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "gtslabs" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>>
>>
>>
>

[gtslabs]

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

[Leith Ross]

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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>