copy the same format to a cell using =

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Del - 18 Sep 2007 15:26 GMT

I am trying to copy a cell from one sheet to another and I use the = function
but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

JE McGimpsey - 18 Sep 2007 15:45 GMT

Functions can only return values, not change cell formats.

To link formats you'd need to use VBA. For example, this will copy the
formats from cells A1:A10 in Sheet1 to cells A1:A10 in Sheet2 whenever
Sheet2 is activated. Put it in the Sheet2 code module (right-click the
Sheet2 tab and choose View Code):

Private Sub Worksheet_Activate()
With Range("A1:A10")
Worksheets("Sheet1").Range(.Address).Copy
.PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
End Sub

> I am trying to copy a cell from one sheet to another and I use the = function
> but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

Doctrine Analyst - 04 Oct 2007 22:11 GMT

JE McGimpsey,

What if the range of cells in the origin sheet (in your example, Sheet1) is
linked to a different range of cells (contiguous or non-contiguous) in the
other sheet (in your example, Sheet2)? How would your formula have to chage?

I have a workbook which tracks 92 project timelines, which each have 27
separate tasks. The top worksheet is a progam summary of the critical
deadlines and looks nothing like the 92 project worksheets. I want them
linked so that the project managers will only fill in data on their project
sheets and not have to duplicate the data entry on the summary sheet. When a
target deadline has been met and the date completed confirmed, I want the
project managers to change the format of the data to bold, underline. I
assume that your formula will solve that, except for the difference in ranges.

Signature

kew

> Functions can only return values, not change cell formats.
>
[quoted text clipped - 15 lines]
> > I am trying to copy a cell from one sheet to another and I use the = function
> > but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

JE McGimpsey - 05 Oct 2007 05:26 GMT

You'd have to have some sort of explicit 1:1 correspondence between the
summary sheet cells and their references. There could be lots of ways to
do it - one brute force way:

With Worksheets("Summary")
Sheets("Project1").Range("B17").Copy
.Range("Z3").PasteSpecial Paste:=xlFormats
Sheets("Project22").Range("R4").Copy
.Range("L5").PasteSpecial Paste:=xlFormats
'etc
End With

> JE McGimpsey,
>
[quoted text clipped - 32 lines]
> > > function
> > > but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

Doctrine Analyst - 05 Oct 2007 14:22 GMT

Thank you for the quick repsonse. Just one more dumb question...do the B17
and R4 cells correspond to the project sheets or the summary sheet?

Signature

kew

> You'd have to have some sort of explicit 1:1 correspondence between the
> summary sheet cells and their references. There could be lots of ways to
[quoted text clipped - 44 lines]
> > > > function
> > > > but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

Peo Sjoblom - 18 Sep 2007 16:13 GMT

If you copy and paste special as link you can carry over the format but it
is a double paste

Copy cell you want to link to, select the cell where you want this to
happen, do edit>paste special and select paste link, then do edit>paste
special again and this time select formats

Signature

Regards,

Peo Sjoblom

>I am trying to copy a cell from one sheet to another and I use the =
>function
> but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

Wigi - 18 Sep 2007 21:34 GMT

Hi Peo

But this method doesn't change the formatting the destination cell when the
formatting in the source cell is changed, no?

I *think* that's what the OP asks for.

Signature

Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

> If you copy and paste special as link you can carry over the format but it
> is a double paste
[quoted text clipped - 6 lines]
> >function
> > but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message

Peo Sjoblom - 19 Sep 2007 01:58 GMT

I don't know if the OP meant that and if he did it obviously won't work

Signature

Regards,

Peo Sjoblom

> Hi Peo
>
[quoted text clipped - 15 lines]
>> >function
>> > but it doesn't copy the same format ie: bold and under line etc.

Reply to this Message