Remove Some Cell Colors After Paste

Hi

Hoping someone can help me out here (again)

This maybe a bit long winded so I hope I can explain it clearly.

I have a number of worksheets in my workbook. Only five of these are visable to the user.

The user can update/edit rows in these visable worksheets.

I then have a button on the main worksheet with code that collates the rows with values from the five different worksheets. (The very first two rows copied are headers which are constant. Just copied for easy reference for the user when looking at the data in the collated sheet. Row 3 onwards contains data which the user had previously been able to change/add)

For info, the worksheet which finally has all the collated info is called 'PrintScn'

When the code finishes doing its bits the 'PrintScn' worksheet appears on the screen with a print button.

All this works very nice. My problem being is .....

The rows of data which get collated have a background colour of Yellow. For printing purposes I'd like the background colour of the rows on the PrintScn to be white ... bearing in mind the first two rows copied from any of the visable worksheets must retain their original colour (for info they are Blue)

Here's the code I currently use (incidentally its code I found on this fantastic site)

Thanks for looking and thanks for helping.

Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code
For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("PrintScn").Range("A65536").End(xlUp).Offset(1, 0)


    Select Case ws.Name
        Case "PrintScn" 'exlude
        'do nothing
        Case "Properties" 'exlude
        'do nothing
        Case "Martin's to-do list" 'exlude
        'do nothing
        Case "Main" 'exlude
        'do nothing
        Case Else
        ws.Activate
        Range("A5", Range("A65536").End(xlUp)).EntireRow.Copy lastRng
    End Select


Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("PrintScn").Activate

Re: Remove Some Cell Colors After Paste

Hi Dave

Thanks for the reply.

I've tried the new code, but I dont think I may have explained enough.

The code runs through fine. But only the first cell of the row turns white. The rest stay yellow.

Also the worksheets the users update may have multiple lines of data which move/merge to the 'PrintScn' worksheet. Where this has happened only the first cell of the first row turns white. The rows immediately below all stay yellow.

I hope this explains. I'd be very grateful if you can have another look.

Regards