Automatically copying formatting

[roniaelm]

Hi,

I have a worksheet (sheet 1), I would like to copy both text and
formatting automatically to another worksheet (sheet 2) in the same
file. I can copy the text easily by referencing to the applicable
cells (e.g. =A2), however, this does not copy the formatting
over.Ongoing formatting changes will be completed for sheet 1 which
can vary from change in cell colour, font colour or font style
depending on the cells and I want these changes to always be reflected
in sheet 2. Is there a macro I can use to perform this?

Thanks!

 

[Susan]

you could do it with a macro, but you could also do it with a simple
additional mouse click.
copy
paste special
paste formats
(then, in the same location, hit)
paste
(again to paste the information).

there is no way to LINK the formatting changes, though, so that if you
change the formatting on sheet 1 it would be instantly updated on
sheet 2...... you'd need a macro for that one.
post back if you want macro samples.

susan

 

[roniaelm]

you could do it with a macro, but you could also do it with a simple
additional mouse click.
copy
paste special
paste formats
(then, in the same location, hit)
paste
(again to paste the information).

there is no way to LINK the formatting changes, though, so that if you
change the formatting on sheet 1 it would be instantly updated on
sheet 2...... you'd need a macro for that one.
post back if you want macro samples.

susan





- Show quoted text -

Hi,
Thank you for replying. I actually have copied the formatting to sheet
2 using the paste special options. But yes, what I need is the a macro
to constantly update sheet 2 as formatting changes in sheet 1 are
applied. I would love to have some samples!
Thanks!

 

[Susan]

i was not sure how to do this, so i researched it in the newsgroup.
after researching, i still don't know how to do it. :/

i saw some stuff on user defined functions, like this one
http://groups.google.com/group/micr...ies+active+cell&rnum=2&hl=en#03ce4ab50db7c734

and other stuff - this one looks like a possibility:
http://groups.google.com/group/micr...date+formatting&rnum=2&hl=en#9553970f3def0b08

basically the problem is this:

1. you'd have to use a worksheet_change event
2. you'd have to (via macro) determine exactly which formatting has
been applied to the active cell - this is waaaaaay more complicated
than it sounds, for example, these are just SOME of the formatting
variables which can be applied:

With rngCell.Characters(Start:=intStart, Length:=intLen).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 55
Endwith

then you've got bold, italics, interior color, exterior color,
borders, numerical formatting, etc. i couldn't even begin to point
you in the right direction of how to extract this info. maybe some of
the guru's can??
3. then you'd have to apply the same formatting to the identical cell
in the other worksheet.

i'm going to change the name of the subject to try to attract
additional attention - perhaps you could post exactly which formatting
properties you'd be changing........ all of them? font only? color
only?

sorry i couldn't help more!
susan

 

[roniaelm]

i was not sure how to do this, so i researched it in the newsgroup.
after researching, i still don't know how to do it. :/

i saw some stuff on user defined functions, like this onehttp://groups.google.com/group/microsoft.public.excel.programming/bro...

and other stuff - this one looks like a possibility:http://groups.google.com/group/microsoft.public.excel.programming/bro...

basically the problem is this:

1. you'd have to use a worksheet_change event
2. you'd have to (via macro) determine exactly which formatting has
been applied to the active cell - this is waaaaaay more complicated
than it sounds, for example, these are just SOME of the formatting
variables which can be applied:

With rngCell.Characters(Start:=intStart, Length:=intLen).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 55
Endwith

then you've got bold, italics, interior color, exterior color,
borders, numerical formatting, etc. i couldn't even begin to point
you in the right direction of how to extract this info. maybe some of
the guru's can??
3. then you'd have to apply the same formatting to the identical cell
in the other worksheet.

i'm going to change the name of the subject to try to attract
additional attention - perhaps you could post exactly which formatting
properties you'd be changing........ all of them? font only? color
only?

sorry i couldn't help more!
susan





- Show quoted text -

Hi,
Thanks for looking into this.
I don't think any of the links are relevant to what I need.
Basically these are the formatting that will mostl likely be completed
by the end user:
- Some cells in particular columns can change in background colour
(this is added at the users descretion)
- Other cells in particular columns can have the text change colour
and/or made bold depending on the value in the cells
(most of this will be applied as a result of the conditional
formatting applied to the columns).
- Also some cells can be merged.
The ideal macro would be one that can reflect all of these changes
sheet 2 as they are made in sheet 1 by checking each individual cell
in sheet 1 and applying the correct formatting to the same cell on
sheet 2? Both sheets are identical in alignment.

Thanks for your help!