I would think all formulas should be locked.
If yes, you can do this manually:
ctrl-a (twice in xl2003) to select all the cells
edit|goto|special|formulas
if just the formulas with the =vlookup() function:
Option Explicit
Sub FindLookup()
Dim FormulaRng As Range
Dim VlookupFormulaRng As Range
Dim FormulaCell As Range
Dim sFormula As String
On Error GoTo NoFormulas
Set FormulaRng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
Set VlookupFormulaRng = nothing
For Each FormulaCell In FormulaRng
With FormulaCell
sFormula = LCase(.Formula)
If InStr(sFormula, "lookup") Then
If VlookupFormulaRng Is Nothing Then
Set VlookupFormulaRng = FormulaCell
Else
Set VlookupFormulaRng = Union(VlookupFormulaRng, FormulaCell)
End If
End If
End With
Next
If VlookupFormulaRng Is Nothing Then
MsgBox "No vlookup formulas found"
Else
VlookupFormulaRng.Locked = True
'VlookupFormulaRng.Select
End If
Exit Sub
NoFormulas:
MsgBox "No formulas found!"
End Sub
JT wrote:
Jason, my mistake. When I say "highlight", I actually meant "select". I want
to select all cells with lookup formulas so that I can use the Format
Cells-Protection-Locked option to protect the cells. Your macro works
beautifully to hightlight (fill background color) the lookup cells, I will
keep it in my notebook. Can you also modify it to let it "select" the cells?
Thanks!
"Jason Morin" wrote:
Try this macro:
Option Explicit
Sub FindLookup()
Dim FormulaRng As Range
Dim FormulaCell As Range
Dim sFormula As String
On Error GoTo NoFormulas
Set FormulaRng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
For Each FormulaCell In FormulaRng
With FormulaCell
sFormula = LCase(.Formula)
If InStr(sFormula, "lookup") Then
.Interior.ColorIndex = 36
End If
End With
Next
Exit Sub
NoFormulas:
MsgBox "No formulas found!"
End Sub
---
To run, press ALT+F11, go to Insert Module, and paste
in the code above. Press ALT+Q to close. Go to Tools
Macro Macros.
HTH
Jason
Atlanta, GA
-----Original Message-----
I need to implement cell protection to all cells with
lookup formula in a
worksheet. I know GOTO-Special can highlight all the
formula cells, but I
need some way more specific to pick and highlight only
the lookup formula.
.
--
Dave Peterson
|