Search a range to find cells containing formulas
 

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

Can anyone help?

Thanks

--
tanktat
-----------------------------------------------------------------------
tanktata's Profile: http://www.excelforum.com/member.php...nfo&userid=459
View this thread: http://www.excelforum.com/showthread.php?threadid=55910

Search a range to find cells containing formulas
 

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" wrote in
message ...

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

Search a range to find cells containing formulas
 

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

Search a range to find cells containing formulas
 

Thanks chaps.

Bob, Yours worked but locked cells that didnt contain formulas. Ha
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

--
tanktat
-----------------------------------------------------------------------
tanktata's Profile: http://www.excelforum.com/member.php...nfo&userid=459
View this thread: http://www.excelforum.com/showthread.php?threadid=55910

Search a range to find cells containing formulas
 

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" wrote in
message ...

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

Search a range to find cells containing formulas
 

Thanks for that, works a treat.

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


--
tanktata
------------------------------------------------------------------------
tanktata's Profile: http://www.excelforum.com/member.php...fo&userid=4598
View this thread: http://www.excelforum.com/showthread...hreadid=559103