How to "store" (in a variable) the cells formats like .Font .Borders .Interior .Alignment .Indent .M

[Gunnar Johansson]

Hi,

To restore formats in a sheet after a worksheet_Change event (cut and paste,
drag and drop etc)., I have a "template sheet"/"master sheet" where I can
retrieve the right format from.

I use today Copy and PasteSpecial together with ScreenUpdating property of
cource, but wonder if there is a way to get rid of the selection, copy and
paste methods? I believe the code could increase it's speed and I feel I
would get better control of things

I'ts about all these formats like font, border etc ( - see below) that
should be restored in an event when a user "try to change" the cell format.

(Yes, I know about coditional formatting, but that doesn't work because the
wrong coditional formatting are being copy - paste too... )

The optimal would be a variable that could represent alALL THIS for Cells
and Ranges.:

Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 37
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Locked = True
Selection.FormulaHidden = False
End Sub

MAYBE A WAY?
Maybe there is a possibility to solve it like following code "store" the
workbook, worksheet and range. I am not skilled enought to do that, but just
throw out this as a possibility to you who know more about this. I'm just a
"code snippet thief"...


Public Sub SaveLocation(ReturnToLoc As Boolean)
' When (True)it "Take in memory" WB, WS, R
' When (False)it activate workbook, worksheet, range
'**************************************
Static Wb As Workbook
Static WS As Worksheet
Static R As Range
If ReturnToLoc = False Then
Set Wb = ActiveWorkbook
Set WS = ActiveSheet
Set R = Selection
Else
Wb.Activate
WS.Activate
R.Select
End If
End Sub

Public Sub SetSaveLoc()
' To save current location workbook, worksheet, range
'*************************
SaveLocation (False)
End Sub

Public Sub GetSaveLoc()
' To restore / activate earlier location workbook, worksheet, range
'*************************
SaveLocation (True)
End Sub



Can any take the challenge? Maybe there is a easy way? I can't be the first
to need this...!?


/Kind regards

 

[BrianB]

The principle is the same as storing a vule in any variable. eg.

Code
-------------------

n = Range("A1").Font.Name
s = Range("A1").Font.Size

-------------------