Search a range to find cells containing formulas

[tanktata]

I am trying to create a macro that will search through a range and protect any cells that contain formulas of any type.

Can anyone help?

Thanks.

[Bob Phillips]

For Each Cell In Activesheet.UsedRange
cell.Locked = Not cell.Hasformula
Next cell

Activesheet.Protect

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"tanktata" <tanktata.2ajp4b_1152225302.1672@excelforum-nospam.com> wrote in
message news:tanktata.2ajp4b_1152225302.1672@excelforum-nospam.com...
>
> I am trying to create a macro that will search through a range and
> protect any cells that contain formulas of any type.
>
> Can anyone help?
>
> Thanks.
>
>
> --
> tanktata
> ------------------------------------------------------------------------
> tanktata's Profile:
http://www.excelforum.com/member.php...fo&userid=4598
> View this thread: http://www.excelforum.com/showthread...hreadid=559103
>

[Jim Thomlinson]

Try this...

Sub ProtectFormulas()
Dim rngFormulas As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1")
On Error Resume Next
Set rngFormulas = wks.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rngFormulas Is Nothing Then rngFormulas.Locked = True
End Sub
--
HTH...

Jim Thomlinson


"tanktata" wrote:

>
> I am trying to create a macro that will search through a range and
> protect any cells that contain formulas of any type.
>
> Can anyone help?
>
> Thanks.
>
>
> --
> tanktata
> ------------------------------------------------------------------------
> tanktata's Profile: http://www.excelforum.com/member.php...fo&userid=4598
> View this thread: http://www.excelforum.com/showthread...hreadid=559103
>
>

[tanktata]

Thanks chaps.

Bob, Yours worked but locked cells that didnt contain formulas. Had would I change the macro so that cells with formulas are locked?

Jim, couldnt get yours to work.

Will these both work with excel 97?

Thanks again.

[Bob Phillips]

Hmm! I will need to look at that.

Try this instead

Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
cell.Locked = True
Else
cell.Locked = False
End If
Next cell

ActiveSheet.Protect

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"tanktata" <tanktata.2akyq1_1152284409.9098@excelforum-nospam.com> wrote in
message news:tanktata.2akyq1_1152284409.9098@excelforum-nospam.com...
>
> Thanks chaps.
>
> Bob, Yours worked but locked cells that didnt contain formulas. Had
> would I change the macro so that cells with formulas are locked?
>
> Jim, couldnt get yours to work.
>
> Will these both work with excel 97?
>
> Thanks again.
>
>
> --
> tanktata
> ------------------------------------------------------------------------
> tanktata's Profile:
http://www.excelforum.com/member.php...fo&userid=4598
> View this thread: http://www.excelforum.com/showthread...hreadid=559103
>

[tanktata]

Thanks for that, works a treat.

Had been trying something along those line but it was the 'HasFormula' bit that i didnt have.