Copying formatting of cells between worksheets

[BurgerUrge]

Hi There,

I'm trying to copy formatting from a range of cells to a range of cells on another work sheet that will automatically update when the first one is changed. I found the below thread and tried to use the VBA scripts in it but to no avail.
http://www.mrexcel.com/forum/showthread.php?t=55333
I Have been trying to do the same but slightly different and unfortuantely my knowledge of VBA is limited. I have read both the scripts and have tried to figure out how to do it myself but have struggled and anyone who could offer me some help it would be greatly appreciated.

I do my staff roster Monday to Sunday colouring in cells which represent start and finish times being I run with 10 staff it gets a little convalouded trying to read it so what I want to do is copy the formatting from one sheet to another for each staff member
So I want to copy formatting from
c3:ac3 in sheet1 to b2:ad2 in sheet3
c19:ac19 in sheet1 to b3:ad3 in sheet3
c35:ac35 in sheet1 to b4:ad4 in sheet3
c51:ac51 in sheet1 to b5:ad5 in sheet3
c68:ac58 in sheet1 to b6:ad6 in sheet3
c84:ac84 in sheet1 to b7:ad7 in sheet3
c101:ac101 in sheet1 to b8:ad8 in sheet3
This will copy one employees roster to the new sheet in order then I want to do it for the remaining 10 employees
c4:ac4 in sheet1 to b10:ad10 in sheet 3
c20:ac20 in sheet1 to b11:ad11 in sheet3
ect ect

If anyone can help I would be muchly appreciated
Cheers
Colby

 

[AlphaFrog]

Do you want to copy just the formats and Not the values or do you want to copy both?

Also, if you want to copy c3:ac3 in sheet1 to b2:ad2 in sheet3, it would have to copy to b2:ab2 in sheet3. The destination range has to be the same size as the source range.

This will copy just the formats and not the Cell values

    Sheets("Sheet1").Range("C3:AC3").Copy
    Sheets("Sheet3").Range("B2:AB2").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False     'Clear the clipboard

 

[BurgerUrge]

Thanks Alphafrog

yes sorry i did mean ab. not ad typo that I copied over. please forgive my ignorance and inability but i opened up the editor and it keeps comeing up with errors. could it be that I have another macro which counts the number of colors on the same file?

thanks for all your help
Colby

 

[BurgerUrge]

The error I get is
complie error
invalid outside procedure

cheers
Colby

 

[AlphaFrog]

It's hard to say what the problem is without knowing what your actual code looks like, what the errors are, and what your other (count colors) code is. Kinda flying blind here.

The three lines of code I gave are really basic.

I doubt you count colors code is the problem but i can't say for sure.

 

[BurgerUrge]

Count Colour script is

Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim Cll As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next Cll
End Function

any thoughts?

do i include your script in this script window or do I open a new one?

Cheers
Colby

 

[AlphaFrog]

Can you post the script (all of it) that has the three lines I gave you?

When it errors and you select DeBug, is one line highlighted? If yes, what line?

Does any of your sheets have Tools\Protection\Protect Worksheet turned on? If yes, can you turn it off and run the macro again?

 

[BurgerUrge]

I had both scripts on different pages. should they be incorporated into one?

when it errors

("C3:AC3") is highlighted

Sheets("Roster").Range("C3:AC3").Copy
Sheets("Staff Hours").Range("B2:AB2").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False 'Clear the clipboard

Sorry for being so incapable

Colby

 

[BurgerUrge]

Is there a way to convert coloured cells into time?

eg
collum c represents 9.30am
Colum D represents 10.00am
Colum E represents 10.30am
Colum F represents 11.00am etc

if row 3 is coloured from colum C to E then it means this staff member (lets call them John) is working 9.30-11am
if row 4 is coloured from colum d to f the it means this staff memeber (lets call them mary) is working 10.00-11.30

would it be possible to convert this data to times somewhere else either on the same sheet or a different one.

E.g.
John 9.30-11.00
Mary 10.00-11.00

or am I asking a little to much of excel?

Cheers
Colby