How do I highlight all lookup formulas in a worksheet in one shot.

J

JT

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.
 
J

Jason Morin

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
 
J

JT

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!
 
D

Dave Peterson

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
 
J

Jason Morin

Hi Dave. I was just about to start modifying the code
when I noticed you took care of it (of course it would
have taken me an hour as opposed to 5 min. <g> for you).
Your code is along the same lines as some old code that
Tom O. provided me using UNION.

http://groups-
beta.google.com/group/microsoft.public.excel.programming/b
rowse_frm/thread/379157f4c930df04/ef6cdbc645ea6b13?

Have a good weekend.

Jason
-----Original Message-----
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
Click to expand...
(VlookupFormulaRng, FormulaCell)
 
J

JT

Thanks, Dave. This macro is great. It even takes care the protection-Locked
part for me. In my case, I need to protect vlookup only. But I believe I can
replace the "lookup" in the macro codes with other "formula name" to perform
the same task. It's a wonderful help!
 
J

JT

Dave and Jason, thanks to both of you. I asked one question this morning, and
within hours I got two perfect answers. I just shared this information with
my coworkers. The helps are greatly appreciated.
 
D

Dave Peterson

Tom is an excellent teacher--I'm sure lots of people/lurkers learned from him (I
know I did!).
 
Top