Results 1 to 7 of 7

Thread: Macro to search for a number and change it "red"
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    553
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    553
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    All the numbers are in one column AD"
    There's 20+ lines.

  3. #3
    New Member davewatson86's Avatar
    Join Date
    Jul 2019
    Location
    Constantly switching between Australia and the UK
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    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
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    553
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    You guys are the best.
    TYTYTYTYYTY

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Macro to search for a number and change it "red"

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •