tenthofjune

The problem

You have a cell in Excel which you’ve got formatted just the way you want it. Then along comes another user and pastes data into that cell; unfortunately, that’s not all that gets pasted, the formatting gets changed too. Now if you were pasting it, you could have avoided this by using ‘paste special…’ and selecting values only, but you can’t force other users to do the same. The ideal solution would have been that Excel could lock the format, but it can’t without locking the cell completely.

The solution

One solution would be to have the user paste into another cell, and have your original nicely formatted cell simply reference the first (and lock it), but that doesn’t always work with your required layout.

Another solution is to have Excel watch for changes in the cell and reformat it back to how you want it. To do this, we need to use VBA code on the sheet.

The first step (unless you are happy writing VBA code to format the cell) is to record a macro of you formatting the cell as you wish.

Select the cell you wish to format, then use ‘Tools -> Macro -> Record New Macro’. Perform all necessary formatting, then press the Stop Button on the Macro toolbar. Don’t worry about naming the macro, it won’t be needed later.

Use ”Tools -> Macro -> Macros…’ followed by ‘Edit’ to see your recorded macro.

It should look something like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/04/2007 by Ian Fitter
'

'
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
End Sub

The bit we’re interested in is the ‘With/End With’ section:

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With

Select and copy this section – we’ll need it later.

We now need to open the code window for the sheet in question. There are a number of ways of doing this, but one is to right-click on the sheet’s tab and select ‘View Code’. This will once again open the VBA code window. Enter the following and paste in your macro from earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

End Sub

Now give it a try. Select and copy a different cell with completely different formatting and paste it into your test cell. Excel will immediately reformat it to your original settings.

Obviously, this would not be necessary if Microsoft had made it possible to lock the format of the cell (instead of wasting its time on the paperclip)

I found the information on monitoring sheet changes on following page:

http://www.ozgrid.com/VBA/run-macros-change.htm