using formulas to transfer cell formatting and comments

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

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

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

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

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.

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.

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.