Cell Interior Color - Copy / Paste

What am I doing wrong ?

The code should copy data / cell formatting (including text colors and cell interior colors) and paste same to the other sheet.
So far I've managed to have it capture the text colors but it refuses to capture the cell interior colors.

Please review the code (the errant portion is offset with '################## toward the bottom of the macro) and
provide advice on how to edit the code.

Thank you.

Please Login or Register  to view this content.

Error code received : Object doesn't support this property or method.

Referencing this line of code : .Interior.Color = cell.DisplayFormat.Interior.Color ' Copy cell background color

Can you advise of a correction to the code ?

TMS thank you for your assistance.

The .DisplayFormat.Interior.Color command is generating an error : Object doesn't support this Object or method.

Sigh .......

If you run the attached modified version you can see the last page turns almost blank. The data has been
copied pasted correctly but ...

Not certain what I've done wrong.

The text colors and the cell interior colors are not transferred from "Schedule by LD" to "LD by Day".

This portion of code is not doing it's job as intended.

Please Login or Register  to view this content.

I know it appears weird but each sheet has a different purpose and all are needed in the project.

What I've tried so far to make the third sheet easier to work with:

Converting Column D to numbers on the first sheet at the very beginning of the processes. (Completed)
Sorting the first sheet (by Col D) prior to anything else occurring. (Completed)
Copying/Pasting first sheet to second sheet. (Completed)
Sorting the second sheet (by Col D). (The macro won't sort properly. However, if I go to the DATA tab and manually sort the second sheet by Col D the manual sort is accurate.)
Copying/Pasting the second sheet to the third sheet. (Completed)
Sorting the third sheet (by Col D). (The macro won't sort properly. However, if I go to the DATA tab and manually sort the third sheet by Col D the manual sort is accurate.)
The third sheet refuses to carry over the cell interior colors when copy/pasting from second sheet to third sheet. Invariably the third sheet has all the correct data pasted but
the entire sheet is 'covered over' with a darkened mask.

At this point (and I am about to check this now) I believe the workbook has been corrupted and needs to be re-built from scratch. If, after re-building the workbook, I still
experience the same problems I will decide to 'chuck' the workbook into the Excel garbage can and move on to something more agreeable. I can't waste my time on
something that continually creates a brick wall to run into.

???

What if you make these changes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Sintek:

I'm receiving an error code : Subscript Out Of Range

Offending code line : X = Replace(.FormatConditions(i).Formula1, """", "")

ByteMarks:

Your suggestion copies everything to each sheet but doesn't transfer the cell colors.

6StringJazzer :

Excel says it wants a variable. See comment below ...

Please Login or Register  to view this content.

Sintek :

I tried both your macro and mine.

Mine fails. Yours gives me a 'Subscript out of Range' error.

As an aside comment, I tried rebuilding the workbook from scratch and oddly enough I am still experiencing the same problems.

At this point it isn't worth spending any more time on the project. Having lost it won't be a life changing event.

I want to thank all of your for trying to assist. Sometimes you gotta '"now when to hold 'em. Know when to fold 'em."

You all have a great and blessed day!

Sintek ... here are two screen shots showing what the LD by Day sheet looks like after I run MY macro.


Image #1 shows the sheet as it first appears :

LDbyDayDarkScreen.jpg


Image #2 show the sheet if I left click and drag over a range of cells, effectively 'highlighting' those cells :

LDbyDayHighlighted.jpg

Did you mean #13 ?

#23 doesn't have any code.

Here, your macro gives me a "Subscript out of range" error, highlighting the following line of code :

Please Login or Register  to view this content.

[/COLOR][/B]


My macro still provides the last sheet with that darkened 'mask' covering over all of the pasted data. It looks like the image
in Post #25 / Second image.

You have not yet answered...Does this happen with the same sample file you uploaded...

Yes



Also...Did you look at the output result in Post 23...is it correct...

The layout of the worksheet is correct.

This is interesting.

Change that line of code to your last suggestion provided the impetus for the macro to copy and paste all sheets as desired. Except, the last sheet still
is not receiving the row colors from the first sheet. The red text is there in the appropriate cells but the cell interior colors are missing.

With the new line of code it still produces the same error here, however I added the dreaded 'On Error Resume Next' statement prior to that segment
of code and the macro runs through smoothly.

I still don't understand why the cell interior colors are not produced in the last sheet.

Please Login or Register  to view this content.

Your macro correctly copies/pastes the data into the last sheet. The colors did not transfer.

Error code : Object doesn't support this property or method.

Offending code line : Ele.Resize(, 3).Interior.Color = Sheets("Schedule By LD").Cells(Ele.Row, 4).DisplayFormat.Interior.Color

Thank you for putting so much effort into the corrections. I am SO sorry this didn't work out considering your efforts.

Have a great day Sintek !

Only FORMAT CONDITIONS under 'Classes'.

Running 2007 on this project.

Thank you.

Sintek:

Here is my parting solution. I decided to go about everything a completely way. Using your macro that does all the copying / pasting ... I added a few more macros
that colored the cells in the last sheet and then another macro that selects cell A1 in all sheets.

Again, thank you so much for your patience, effort and overall assistance. Well done !

Please Login or Register  to view this content.

That is definitely faster.

Does the coloring of cells in the last sheet.

It missed copying data to the 2nd sheet. ???

I find it difficult to follow that macro due to it's syntax.

Too may commas.

Ok ... I was able to duplicate part of the macro and get it to copy / paste to the second sheet as well.

Guess I'm learning something from this exercise.

Originally Posted by Logit

My macro still provides the last sheet with that darkened 'mask' covering over all of the pasted data. It looks like the image
in Post #25 / Second image.

Because in this function the colour is assigned to the variable "getColorIndex" and not the function "getAppliedColorIndex". Therefore the returned result was always zero, i.e. black.

Please Login or Register  to view this content.

ByteMarks

I am unsure where to use the function you provided and how to edit the existing code so it is implemented.

Assistance ?

I didn't provide it. It's in the file attached to Post #7