Locking multiple cells (F4 key)

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

You could use Find and Replace (CTRL-H), depending on what your
formulae look like. In your example above, you could:

Find What: A
Replace with: $A$

(or A$, depending on the circumstances)

Hope this helps.

Pete

kestrel wrote:
> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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!