Thread: Preserve conditional formatting when copying cells from one sheet to another

Hi,

In my three sheet workbook, the second and third sheet contain project statuses ('Live and Completed') and the main sheet grabs all the projects that are currently "Live", when i hit the "Update" button.

The second and third sheet also contain the 'planned' start and end dates of a project and the 'actual' start and end date of the project, (shown as dates and as a gantt chart). Conditional formatting is applied here (green bars for live projects and red ones for completed projects)

Now conditional formatting works well on these two sheets but when i try to copy all the Live rows into the main sheet, the rows without 'actual' start and end dates goes bonkers (it shades the entire row).

Is it possible to preserve the formatting just as it was in the sub sheets?

And i got the code to copy the rows somewhere on the net and its very slow when i have more than 20 rows. Is it possible to optimize the code as well??

Please look into the attached sheet, so that you can understand my problem better.

Thanks in advance for all the help.

You need to make sure the data starts in the same rows on all sheets (since the CF rules look at row 2 for the dates), so either insert some rows in the detail sheets or delete the rows at the top of the summary sheet.

Thanks for the reply. I inserted a couple of rows to my other two sheets, i can now see the 'Planned' formats but i still see the dark green format applied to the entire row.

This is the code that i use to copy the rows to my main sheet.

Sub PickRows1()
    Dim Source As Worksheet
    Dim Dest As Worksheet
    Dim WatchCol As Range
    Dim cel As Range
    Dim WatchFor

    Set Source = ThisWorkbook.Worksheets("Analyst 1")
    Set Dest = ThisWorkbook.Worksheets("Summary")
    Set WatchCol = Source.Range("I4:I1000")
    WatchFor = "Live"
    Range("a7:i50").ClearContents
    For Each cel In Intersect(Source.UsedRange, WatchCol).Cells    
        If cel = WatchFor Then
            cel.EntireRow.Copy Destination:=Dest.Range("A1000").End(xlUp).Offset(1, 0)
        End If
    Next  
End Sub

Can this code be modified to pick the first nine cells of every row (from Analyst 1 and Analyst 2 sheets) instead of the entire row and then apply conditional formatting?

Change the Copy line to:

Source.Cells(cel.Row, "A").Resize(, 9).Copy Destination:=Dest.Range("A1000").End(xlUp).Offset(1, 0)