Locking multiple cells (F4 key)

Kestrel,

Select your cells, then run the macro below.

HTH,
Bernie
MS Excel MVP


Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
Dim RefStyle As Variant
Dim MyMsg As String
Dim myStyle As Integer

MyMsg = "1: =A1 Relative" & Chr(10) & _
"2: =A$1 Absolute Row" & Chr(10) & _
"3: =$A1 Absolute Column" & Chr(10) & _
"4: =$A$1 Absolute" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, 3, or 4...."
myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)

Select Case myStyle
Case 1
RefStyle = xlRelative
Case 2
RefStyle = xlAbsRowRelColumn
Case 3
RefStyle = xlRelRowAbsColumn
Case Else
RefStyle = xlAbsolute
End Select

With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual

For Each myCell In Intersect(Selection, Selection.SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, RefStyle)
Next myCell

.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With


"kestrel" <kestrel.2cj69r_1155560105.8666@excelforum-nospam.com> wrote in message
news:kestrel.2cj69r_1155560105.8666@excelforum-nospam.com...
>
> I'm often locking a formula in a cell so that the cell reference number
> doesn't change upon copying and pasting.
>
> E.g.
>
> =A2+5
> becomes
> =$A$2+5
> or sometimes I choose
> =A$2+5
> etc.
>
> I need to do this for around 100 different cells, and then change the
> lock on them.
>
> Is there a quick way I can lock multiple cells in the same way, rather
> than going through each one and pressing 'F4'?
>
> Thanks
>
>
> --
> kestrel
> ------------------------------------------------------------------------
> kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082
> View this thread: http://www.excelforum.com/showthread...hreadid=571354
>

10 years on and still very helpful - Thank you!