FAO Stoner and other Excel geeks, sorry, power users

Can I do a formula that will copy a cell along with it’s formatting?

So, very basically, say cell A1 is in bold type and filled in red to highlight something or other.

if I put =A1 in another cell, I just get the value of A1 and not the formatting. Is there a super-cool-bags-you-all-the-office-chicks of getting the formatting too?

You know you can copy the format with the format painter dont you? Does it have to be with a formula?

http://www.howtogeek.com/howto/14165/copy-excel-formatting-the-easy-way-with-format-painter/

Yeah, I know about format painter, won’t do what I need.

Basically I have a summary sheet at the front of a multi-tab workbook. Certain cells are brought through from the ‘working’ sheets, and those ‘working’ cells may be highlighted certain colours to denote certain things. I’d like to bring those colours through to the summary sheet.

Tried conditional formatting?

IHN had a bit of a play. I know what you want but I dont think you can do it.

I think you are going to have to use VBA to copy the cell format.

conditional formatting is probably the best way but it really depends on what you want to do. If you just want to copy 1 cell format, paste special, if it is a whole spreadsheet you can write a nested formula in conditional formatting.

Copy – Right click – Paste Special – Values

then

Right click – Paste Special – Formats

Or if it’s auto linked, Conditional Formatting.

(He says, having read the question in full, somewhat belatedly)

Think what IHN wants its a formula where you can say =formula(sheet1!A2)

I couldn’t see a way of doing it easily in VBA but only spent 10mins.

I’m certain it can be done in VBA but I have never taken the trouble to learn it, just bastardise the work of others. I say it can be done because I’m certain that within VBA you can Copy a cell or range then PasteSpecial, PasteFormats.

If I’m reading the OP correctly he wants a formula to reproduce the format of a cell that may change from time to time, not a one off copy and paste.

Function format_copy(my_cell)

range(my_cell).Copy
ActiveCell.PasteSpecial xlPasteFormats
ActiveCell.PasteSpecial xlPasteValues

End Function

doesn’t work. I cant think why though.

Cheers ladies and gents, I just did it the long way in the end 😕

soma_rich – Member

Stuff

doesn’t work. I cant think why though.

Formatting can’t be applied through functions/formulae.

Conditional formatting is what you’re after.
A formula =A1<>”” (for instance) applied to any cell & conditionally formatted will change the formatting of that cell when A1 isn’t blank.

Very powerful tool, espesh. since it was updated in 07 – very complex formulae can be applied and it can get stupidly confusing.

Oh, and just as I was recording a macro to get

Range(“A1”).Select
Selection.Copy
Range(“A2”).Select
ActiveSheet.Paste

if I keep working on this I may know how to do it in time for next month.