Configure Excel to look by default in Formulas, Values and Comments

Question

I use comments allot in Excel and I need to search for a particular text that is in both values and comments. Is there a way to configure the Find and Replace in Excel to look by default in these fields?

Answer 1

I do this: For my workbook, go to VBA. Under Microsoft Excel Objects > ThisWorkbook

Option Explicit
Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Worksheets(1).Cells.Find What:="", After:=ActiveCell, _
    LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False
    Application.ScreenUpdating = True
End Sub

So, whenever the workbook loads, it will run this code which basically do a dummy search and set the LookIn to Values (from Formula). Excel tends to "remember" your last setting. Thus your future CTRL-F will use this setting.

I know you can put a dummy workbook which auto-loads each time Excel loads, but I prefer to set it for specific workbook instead. Hope this helps.

Answer 2

You can try Advanced Find & Replace for Microsoft Excel.

Search and replace in all opened Excel workbooks and worksheets. Simultaneous search in values, formulas, hyperlinks and comments.

"Advanced Find & Replace for Microsoft Excel" enhances Excel's built-in Find and gives you:

• Find and Replace in all opened Excel workbooks & worksheets - Advanced Find & Replace for Excel
• Simultaneous search in values, formulas, hyperlinks and comments.
• Search in all opened or selected workbooks and worksheets.
• All search results displayed in a tree-view.
• Easy navigation in search results.
• Replacing in all or selected found items.
• Exporting found instances to a new workbook.
• Show / hide the add-in easily (standard Ctrl+F shortcut).
• Support of Microsoft Excel 2007.

Before the search starts, you define the search region by selecting workbooks, worksheets, comments, hyperlinks, formulas or/and values.

Download the 15-days fully-functional trial version (3 MB)