Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How do I highlight all lookup formulas in a worksheet in one shot.
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 (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 . |
#6
|
|||
|
|||
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. "Jason Morin" wrote: 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 (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 . |
#7
|
|||
|
|||
Tom is an excellent teacher--I'm sure lots of people/lurkers learned from him (I
know I did!). Jason Morin wrote: 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 (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 . -- Dave Peterson |
#8
|
|||
|
|||
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! "Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Lookup last in column formulas | Excel Discussion (Misc queries) | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Highlight formulas | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |