Copying formatting

[vko]

Hi... I'm new to this forum, but have learnt a lot from it in the "past"
I now have a thing that I can't solve on my own - I have, say ten sheets in a workbook, and the first one is there to store info to be used in the other sheets.
I now want to have a range of cells coloured in the same colour on all or some sheets, and should therefor make a cell in the infosheet that colour to get from (=infosheet A1)
This command will not copy the colour but only the data in that cell. I have been told from my workmates, that there's no way to do this.
That I don't believe, on a complex program as excel this is no problem. Only thing is that I don't know how to do it.
Is this possible, and how - please let me show my collegues that i'm not that thick and let me use my time to other things than colour areas manually. Thanks in advance.
I'm using excel 2003

 

[ravishankar]

Hi
It is possible with a macro (not formula). You have not mentioned the range to be copied and the colour that goes with it. paste the following code in the macro window ( press Alt F11 together - Right side frame)
and run the macro. it will copy A1 cell of infosheet and pastes to A1 of denmark sheet (change sheet name to suit your case)

Sub Macro1()
    Sheets("infosheet").Range("A1").Copy
    Sheets("Denmark").Range("A1").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
        MsgBox "complete"
End Sub

Ravi

 

[vko]

Thanks Ravi - that was quick

That does the trick allright - but not good enough.
What I'm looking to do is to define some cells in "denmark" "denmark1""denmark2" etc. (not always the same cells) to be colourcoded when printed. To do this I would set i.e. A1 in the "infosheet" to be green, and thus would the defined cells also turn green. Next time I might need a red colour and I would turn "infosheet A1" red. Macros are good, but if I have to run them on each sheet, I could just aswell colour the cells manually.
Please help me.

I've noticed that if you copied the entire colomn or row the formatting follows including colours.
Can I define a colomn or row to be like colomn A on "infosheet" ??

 

[ravishankar]

Hi
try this macro. It will answer some of your question

Sub Macro1()
Dim a As Long
Dim b As String
For a = 2 To 5 'replace 5 with sheets.count
b = Choose(a, "A1", "C5", "G11", "H21")
    Sheets("Infosheet").Range("A1").Copy
    Worksheets(a).Range(b).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
        Next a
        MsgBox "complete"
End Sub

ravi

 

[vko]

Thanks Ravi...
This will do the trick, and I think I got the idea.

Thanks - check your PM

 

[vko]

Ravi..... THANKS

Problem solved - I can only recommend this board.

Thanks again.

Bill