Is it possible to keep a text/cell color/formatting in general in a reference?

[Hollywoood]

Hi all,

I am trying to populate one worksheet with values from several others which is easy enough. I then have formulas to perform opperations on this sheet based on the cells text color or fill color or bold/italic etc. These also work fine on their own.

What Id like to do is have the final worksheet populate automatically from the source sheets but to keep their cell formatting be it text color, fill color, etc. Is something like this possible? So the very basic would be set cell B1 = A1 but not just contents but also formatting.

Thanks in advance!

 

[Jerry Sullivan]

Hi Hollywood,

I am trying to populate one worksheet with values from several others which is easy enough.

What process or VBA code are you currently using to do this?

If you are using VBA code, can probably modify it slightly to use any one of the methods below
to copy both Values and Formats together.

SourceRange.Copy Destination:= TargetRange

SourceRange.Copy
TargetRange.PasteSpecial (xlPasteAll)

SourceRange.Copy
TargetRange.PasteSpecial (xlPasteValues)
TargetRange.PasteSpecial (xlPasteFormats)

 

[Hollywoood]

Thanks for the reply Jerry, I hadnt actually explored VBA yet as I am not particularly versed in it so I had hoped there might have just be a special copy command I havent come across. Doesnt sound like it though.

 

[Jerry Sullivan]

Thanks for the reply Jerry, I hadnt actually explored VBA yet as I am not particularly versed in it so I had hoped there might have just be a special copy command I havent come across. Doesnt sound like it though.

How are currently populating the cells?
Your formatting should be copied with the values with a manual Copy and Paste process.

 

[Hollywoood]

A few different ways JS but mostly simple =IF(...) statements relying on date or value criteria. The number is being copied but I have other formulas that rely on it being bold/italic/regular or its text/fill color.

The problem is that everything is being copied as regular default black arial size 10 etc.

 

[Jerry Sullivan]

Ok- now I understand that you are populating the cells in the Target Worksheet with formula references to the Source Worksheet.

No, there isn't a way to have your worksheet formulas transfer the cell formatting of the referenced.

You can use conditional formatting to change the formatting of the your cells based on the value. This isn't the same as transfering the formatting from the referenced cells, but if you have a small number of cases it might work.

The number is being copied but I have other formulas that rely on it being bold/italic/regular or its text/fill color.

Can you give an example of one of those formulas?
The CELL() function can read font property, but I'm not aware of any worksheet formulas that read fill color (without using VBA).