Easy Question Re: Copying Formatting Between Worksheets

[robertuva]

I know this is easy, but duh...I can't figure it out. I am trying to copy the formatting from one worksheet to another, but I want that formatting to change on worksheet 2 when the formatting on worksheet 1 changes. So let's say I have a cell that is red in worksheet 1. I want it to be red in a specified cell in worksheet 2. If I change the formatting to green in the cell on worksheet 1 it should change also in worksheet 2.

Please help the helpless!

Thanks,
Bobby

 

[armando]

Hi1;

On the first worksheet, give a click to the rectangle above the number 1 indicating first row (the rectangle is at the left of letter A that indicates column A. Then copy, and select the same rectangle in the second worksheet and then, Edit/Paste Special/Format OK.

A faster way is using the button "copy format" instead of copy. Then when you select the rectangle in the second worksheet you'll have the format copied.

Regards

 

[robertuva]

Yea, but if I then change the format in worksheet 1 with this method it won't change it in worksheet 2. I would have to manually go back and do this again.

 

[Yogi Anand]

Yea, but if I then change the format in worksheet 1 with this method it won't change it in worksheet 2. I would have to manually go back and do this again.

Hi robertuva:

For the change to be effected automatically, we need VBA. The following code uses the Worksheet_SelectionChange event ...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target = [a2] Then [a2].Copy [sheet2!A2]
End Sub

when the color of cell A2 in the active worksheet changes, that change is also efected in cell A2 of sheet2.

I hope this helps. If I have misunderstood your question, my apologies!

 

[Chitosunday]

I think it is better to use the worksheet_activate method than the selectionchange because the selectionchange is activated when you enter the specific cell. Right click the sheet2 name below your display then view code and delete or clear any data in the view code and write this:Private Sub Worksheet_Activate()
Sheets("Sheet1").Columns("A:A").Copy
Application.ScreenUpdating = False
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("a1").Select
Application.ScreenUpdating = True
End Sub

 

[BurgerUrge]

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 for 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

 

[Burrgogi]

BurgerUrge,

It's proper board etiquette to start your own thread instead of jumping into the middle of another person's question. In this case, since Robertuva's question has already been answered, there's not too much confusion that would arise from posting your question here. But this thread dates back 7 years and there's not much of a point in reviving such an old thread. You can certainly post a new thread and reference this one... e.g. "I've tried the scripts in the following thread.... but I have problems x, y, z...etc."

 

[BurgerUrge]

Sorry burrgogi,

Just didn't want it to seem like I hadn't tried to work it out myself or look arround before posting my question. will start a new thread now