katana_flyer
New Member
- Joined
- Feb 19, 2020
- Messages
- 17
- Office Version
- 2013
- Platform
- Windows
Hi everyone, been looking through other posts and have found similar, just can't seem to quite get it right.
I'm trying to find all cells containing a certain number, then highlighting them.
This code that I found on another post, nearly works but the problem I'm finding is if I enter "1" as the search number, it is finding all cells with 1 and also any number containing 1, such as 12, 13,110 etc.... Can anyone offer a modification to help sort it out?
(I'm a beginner so it may be an obvious answer to most)
Thank you
I'm trying to find all cells containing a certain number, then highlighting them.
This code that I found on another post, nearly works but the problem I'm finding is if I enter "1" as the search number, it is finding all cells with 1 and also any number containing 1, such as 12, 13,110 etc.... Can anyone offer a modification to help sort it out?
(I'm a beginner so it may be an obvious answer to most)
Thank you
VBA Code:
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 with a value of ...", "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 with value of : " & fnd
Exit Sub
'Error Handler
NothingFound:
MsgBox "No cells containing: " & fnd & " were found in this worksheet"