Macro to search for a number and change it "red"

MikeyZ

Well-known Member
Joined
Mar 14, 2002
Messages
553
I have a string of numbers in a cell and I need a macro to search for the number 530BA and turn it red.

numbers look like the following: 422EDA 651GCB, 525TAA, 530ZBA, 411ZXP, 662MGA, 422EDA, 530ZBA, 411ZXF, 420LDA, 525KEA.

There could be 20+ number in a cell.

Is there simple macro I can run to do the trick?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try this

Code:
Option Explicit
Dim FindString As String
Dim Rng As Range
Sub Find_First()
    
    
    
    'use FindString = InputBox("Enter a Search value") if the value you want to find changes
    'or FindString = range("A1").value for when you want to find the value entered in cell A1
    
    FindString = "530BA"
    
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("AD1", Range("ad" & Cells.Rows.Count).End(xlUp))
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Rng.Interior.Color = RGB(255, 0, 0)
                Rng.Select
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub
 
Upvote 0
I've just tested this and it seems to do what you need.

As always, please test on a copy of your data file before using on live data just in case of unexpected results!

Code:
Sub set_to_red()
'reset font colour of selection to automatic
    Selection.Font.ColorIndex = xlAutomatic
'specify string to search for
    search_string = "530BA"
'loop through each cell in range and set search string to red if found
    Application.ScreenUpdating = False
    For Each c In Selection
        search_string_start = InStr(1, UCase(c), search_string)
        search_string_length = Len(search_string)
        If search_string_start > 0 Then
            c.Characters(Start:=search_string_start, Length:=search_string_length).Font.ColorIndex = 3
        End If
    Next c
End Sub
 
Upvote 0
If you can have the string more than once in a cell, try
Code:
Sub MikeyZ()
   Dim Cl As Range
   Dim Strt As Long
   Dim Srch As String
   
   Srch = "530ZBA"
   For Each Cl In Range("AD2", Range("AD" & Rows.Count).End(xlUp))
      Strt = InStr(1, Cl, "530ZBA", vbTextCompare)
      Do Until Strt = 0
         Cl.Characters(Strt, Len(Srch)).Font.Color = vbRed
         Strt = InStr(Strt + Len(Srch), Cl, Srch, vbTextCompare)
      Loop
   Next Cl
End Sub
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top