You are here:

Excel/VBA code - Search all cells in a worksheet and highlight the cells that contain a user entered value

Advertisement


Question
The VBA code below will search all cells in a worksheet and highlight the cells that contain a user entered value. But If the user entered value is the result of a formula on the worksheet, it is missed out.
For example, I want to highlight all cells in the worksheet containing 500.
The VBA code picks up all cells containing the value 500, but not the cell having a formula : =Sum(400+100).
In this example I am using a simple formula, but in the actual spreadsheet the formulas are complex. I can do this by two different ways; (1)Using conditional formatting  (2) Use Paste Special and convert all formula to values and then run the code.
But can I tweak this code to highlight the formula results too.

Sub HighlightSpecificValue()
'PURPOSE: Highlight all cells containing a specified values
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
'What value do you want to find?
 fnd = InputBox("I want to hightlight cells containing...", "Highlight")
   'End Macro if Cancel Button is Clicked or no Text is Entered
     If fnd = vbNullString Then Exit Sub
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
 If Not FoundCell Is Nothing Then
   FirstFound = FoundCell.Address
 Else
   GoTo NothingFound
 End If
Set rng = FoundCell
'Loop until cycled through all unique finds
 Do Until FoundCell Is Nothing
   'Find next cell with fnd value
     Set FoundCell = myRange.FindNext(after:=FoundCell)
   'Add found cell to rng range variable
     Set rng = Union(rng, FoundCell)
   'Test to see if cycled through to first found cell
     If FoundCell.Address = FirstFound Then Exit Do
 Loop
'Highlight Found cells yellow
 rng.Interior.Color = RGB(255, 255, 0)
'Report Out Message
 MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd
Exit Sub
'Error Handler
NothingFound:
 MsgBox "No cells containing: " & fnd & " were found in this worksheet"
End Sub

Above is not my creation. I got it from the web

Answer
Joe,

Find actually has several arguments and one of them is whether to lookin xlValues or xlFormulas.  I added an argument to look at xlValues and that worked for me.

Sub HighlightSpecificValue()
'PURPOSE: Highlight all cells containing a specified values
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
'What value do you want to find?
fnd = InputBox("I want to hightlight cells containing...", "Highlight")
'End Macro if Cancel Button is Clicked or no Text is Entered
If fnd = vbNullString Then Exit Sub
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, _
   after:=LastCell, _
   LookIn:=xlValues, _
   LookAt:=xlWhole)
'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)
'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)
'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do
Loop
'Highlight Found cells yellow
rng.Interior.Color = RGB(255, 255, 0)
'Report Out Message
MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd
Exit Sub
'Error Handler
NothingFound:
MsgBox "No cells containing: " & fnd & " were found in this worksheet"
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.