MrExcel Publishing
Your One Stop for Excel Tips & Solutions

using formulas to transfer cell formatting and comments


Posted by Coline on January 23, 2002 3:19 PM

Hi, I would like to use formulas (e.g., vlookup, index, match) to transfer not only the contents of cells, but also formatting and perhaps cell comments. Is there an easy way to do this?
Thanks!
Coline


Posted by Joe Was on January 23, 2002 3:22 PM

The only way I know of is a special paste VBA macro. JSW

Posted by Coline McConnel on January 23, 2002 3:27 PM

Do you have one of these set up that I could use as a template as I know 0 visual basic? I'd appreciate the example if you have one handy.
Thanks!
Coline

Posted by Joe Was on January 23, 2002 3:34 PM

Sub sPaste()
'This code pastes the format and value of the current selection,
'to the active range. The option has been set to Hot-key Ctrl-s

ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


This only pastes you need to have some range copied to the clip-board. It pastes to the current selected cell. Also it currently only pastes the format and the value you can add the comments as Validation. Any option on the Special Paste form can be used in this code. JSW

Posted by Joe Was on January 23, 2002 3:41 PM

This cuts to sheet2 and pastes all formatting and validation (Comments).

Sub CopySlect()

'Copy current selection, cell or range.
Application.ScreenUpdating = False
Worksheets("Sheet1").Select

'Paste Sheet1 data to Sheet2, starting in column B down, add new data to bottom of list.
Selection.Copy Destination:=Worksheets("Sheet2").Range("B65536").End(xlUp).Offset(1, 0)

'Delete current selection.
Application.CutCopyMode = False
Selection.ClearContents
Application.ScreenUpdating = True

End Sub

The
Selection.ClearContents
can be commented out to keep the selection! JSW

Posted by Coline on January 23, 2002 4:26 PM

Hi, Joe, thanks for the code; it helps for some of my problems. However, in some cases, I would like to write a conditional statement, and then if it's true, I would like it to fill in the contents, formatting, and validation from another cell. Do you know how to do this one too? Thanks again.
C This cuts to sheet2 and pastes all formatting and validation (Comments). Sub CopySlect() 'Copy current selection, cell or range.

Posted by Joe Was on January 23, 2002 7:25 PM

Just write a block IF...Then...Else or a branch Case list, direct it to Select the Range you want, then copy and paste to the final destination. JSW Hi, Joe, thanks for the code; it helps for some of my problems. However, in some cases, I would like to write a conditional statement, and then if it's true, I would like it to fill in the contents, formatting, and validation from another cell. Do you know how to do this one too? Thanks again.

Posted by Kevin Goodwin on February 20, 2002 7:04 AM

Sounds like you could try using the Conditional Formating logic.
Under FORMAT button on the main menu. You can set up varies IF tests
and set the cell format for each condition to be
different. You can then use PASTE Special to apply this to other cells.