Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

If you leave the formula in the cell, then you can't use this character by
character formatting.

If you could use values, you could copy|paste.

cdil wrote:
>
> Hello everyone,
>
> I am creating a sub that takes the value of a range into another one as
> a formula
> i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
> So it can be updated automatically when SourceCell changes.
>
> I would like to know if it is possible to copy or get the format of the
> SourceCell to apply it also to the TargetCell.
>
> For example:
> SourceCell value is "Hello John" with the word "John" in bold
> characters.
> Currently, TargetCell will display "Hello John" without the bold
> characters.
>
> How can I do to also get the word "John" in bold characters while
> keeping the "dynamicity" of the cell ?
>
> Thanks you for your help !

--

Dave Peterson

if A1 is in bold but B1 isn't then you can do something like:

range("A1").copy
range("B1").pastespecial xlformats

Rgds
J

On 29 Nov, 15:10, "cdil" <cdil1...@gmail.com> wrote:
> Hello everyone,
>
> I am creating a sub that takes the value of a range into another one as
> a formula
> i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
> So it can be updated automatically when SourceCell changes.
>
> I would like to know if it is possible to copy or get the format of the
> SourceCell to apply it also to the TargetCell.
>
> For example:
> SourceCell value is "Hello John" with the word "John" in bold
> characters.
> Currently, TargetCell will display "Hello John" without the bold
> characters.
>
> How can I do to also get the word "John" in bold characters while
> keeping the "dynamicity" of the cell ?
>
> Thanks you for your help !

Thanks guy for your help
Unfortunatelly with a copy & paste (even by pasting only formats), I
can't preserve the "dynamicity"

Other ideas ?


WhytheQ wrote:
> if A1 is in bold but B1 isn't then you can do something like:
>
> range("A1").copy
> range("B1").pastespecial xlformats
>
> Rgds
> J
>
> On 29 Nov, 15:10, "cdil" <cdil1...@gmail.com> wrote:
> > Hello everyone,
> >
> > I am creating a sub that takes the value of a range into another one as
> > a formula
> > i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
> > So it can be updated automatically when SourceCell changes.
> >
> > I would like to know if it is possible to copy or get the format of the
> > SourceCell to apply it also to the TargetCell.
> >
> > For example:
> > SourceCell value is "Hello John" with the word "John" in bold
> > characters.
> > Currently, TargetCell will display "Hello John" without the bold
> > characters.
> >
> > How can I do to also get the word "John" in bold characters while
> > keeping the "dynamicity" of the cell ?
> >
> > Thanks you for your help !

If the original cell is changing because of typing, you could use a
worksheet_change event to do the copy|paste (and formats) for you.

If the original cell is changing because of a calculation/formula, then you
could use a worksheet_calculate event to do the copy|paste (and formats) for
you.

But both of these mean that the "receiving" cell will not contain a formula.

cdil wrote:
>
> Thanks guy for your help
> Unfortunatelly with a copy & paste (even by pasting only formats), I
> can't preserve the "dynamicity"
>
> Other ideas ?
>
> WhytheQ wrote:
> > if A1 is in bold but B1 isn't then you can do something like:
> >
> > range("A1").copy
> > range("B1").pastespecial xlformats
> >
> > Rgds
> > J
> >
> > On 29 Nov, 15:10, "cdil" <cdil1...@gmail.com> wrote:
> > > Hello everyone,
> > >
> > > I am creating a sub that takes the value of a range into another one as
> > > a formula
> > > i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
> > > So it can be updated automatically when SourceCell changes.
> > >
> > > I would like to know if it is possible to copy or get the format of the
> > > SourceCell to apply it also to the TargetCell.
> > >
> > > For example:
> > > SourceCell value is "Hello John" with the word "John" in bold
> > > characters.
> > > Currently, TargetCell will display "Hello John" without the bold
> > > characters.
> > >
> > > How can I do to also get the word "John" in bold characters while
> > > keeping the "dynamicity" of the cell ?
> > >
> > > Thanks you for your help !

--

Dave Peterson

That solution worked out perfectly
Thanks a lot !


Jim Thomlinson a écrit :

> This is about a close as you will get. It will be dynaic in terms of updating
> the formats when the active cell is moved. If you update the format of the
> source cell or change the address so the source cell the target cell willbe
> updated as soon as the active cell is changed. Place this code directly into
> the sheet (right click the sheet tab and select view code)...
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Range("SourceCell").Copy Range("TargetCell")
> Range("TargetCell").Formula = "=SourceCell"
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "cdil" wrote:
>
> > Hello everyone,
> >
> > I am creating a sub that takes the value of a range into another one as
> > a formula
> > i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
> > So it can be updated automatically when SourceCell changes.
> >
> > I would like to know if it is possible to copy or get the format of the
> > SourceCell to apply it also to the TargetCell.
> >
> > For example:
> > SourceCell value is "Hello John" with the word "John" in bold
> > characters.
> > Currently, TargetCell will display "Hello John" without the bold
> > characters.
> >
> > How can I do to also get the word "John" in bold characters while
> > keeping the "dynamicity" of the cell ?
> >
> > Thanks you for your help !
> >
> >