Freeze Formatting

[Guest]

I'm trying to develop a way to freeze the formatting of a cell or group of
cells (lines and shading). What I need to be able to do is click and drag
the contents of a cell to another cell while maintaining the original
formatting of the originating and destination cell.
-Shawn

 

[Dave Peterson]

Maybe you could catch the change, save the new formula/value, do an undo and
then reapply the formula/value.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

This is an event macro. Rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window. Then back to
excel and test it out.

But that breaks if macros are disabled or events are disabled.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm