transferring formats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jonas.ornborg - 22 May 2006 11:11 GMT

Hi,
I have two worksheets in the same workbook where one sheet have
references to the other to transfer values and text. However, I would
also like to transfer the formats togheter with the values
automatically. How do I do this?

All the best
Jonas, Sweden

Signature

jonas.ornborg

Reply to this Message

Pete_UK - 22 May 2006 11:31 GMT

A formula can only return a value, not a format. You can copy a format
from one or more cells to others using the Format Painter - select the
cell(s) whose format you want to copy then click the Format Painter
icon then click on the cell to copy the format. Or you can use Copy
then Edit | Paste Special | Formats (check) | OK.

Hope this helps.

Pete

Reply to this Message

jonas.ornborg - 22 May 2006 11:42 GMT

Pete_UK Wrote:

> A formula can only return a value, not a format. You can copy a format
> from one or more cells to others using the Format Painter - select the
[quoted text clipped - 5 lines]
>
> Pete

Hi,
Thanks but I would like the format to be connected with the value to be
transfered by the formula and therebye avoid a manual transfer of
formats. But if this is not possible I have to find another solution.

Thanks,
Jonas

Signature

jonas.ornborg

Reply to this Message

Pete_UK - 22 May 2006 11:56 GMT

Thanks for the feedback - you could do what you want by means of a
macro in VBA.

Pete

Reply to this Message

jonas.ornborg - 22 May 2006 12:42 GMT

Hi again Pete,
do you know if it would be possible to write a macro that change
cellpattern according to if a number in a column is even or uneven? I
have a database with objects that occupies more then one row and I
would like every second to appear in a different colour to distiguish
between objects.

J

Signature

jonas.ornborg

Reply to this Message

Pete_UK - 22 May 2006 13:01 GMT

Have a look at this page from Chip Pearson's site:

http://www.cpearson.com/excel/banding.htm

It covers colour banding and might give you some tips.

Pete

Reply to this Message

jonas.ornborg - 22 May 2006 14:35 GMT

Excellent tip! Unfortunatly it wont work for me since my "objects"
consist of irregular number of rows and as I understood it, the formula
given on the site only allowed for banding with regullar number of
rows.

I have a column with unique values and I hoped I could used this one to
identify unique objects and then band them according to this.

Any suggestions?

J

Signature

jonas.ornborg

Reply to this Message

Pete_UK - 23 May 2006 13:35 GMT

Jonas,

if you are still checking this thread, you might find the following
link to be of use. David McRitchie shows how you can colour a row if a
certain column contains a negative value - perhaps you can work from
this:

http://www.mvps.org/dmcritchie/excel/colorrng.htm

Hope this helps.

Pete

Reply to this Message