Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

VBA code that formats a word (that word only) if it is present in each cell of a specified range

How do I write a code that says in effect: Look for the string "27D" inside each cell in a specidied range, and make that string Red Bold? This sample code is just to give you a better idea of what I'm looking for

Thanks,
John

Sub FormatText()
Dim str As String
'str = find "27D" within the cell and make it Red Bold
For Each cell In Range("B2:B200")
str.Font.ColorIndex = 3 'I know this is all wrong
str.Bold = True 'I know this is all wrong

Next
End Sub

Open in new window

0
gabrielPennyback
Asked:
gabrielPennyback
  • 2
1 Solution
 
SiddharthRoutCommented:
Is this what you want? Please replace Sheet1 with the relevant sheet name.

Sub ColorCells()
    Dim SearchString As String
    Dim aCell As Range, bCell As Range
    Dim ExitLoop As Boolean
    
    SearchString = "27D"
    
    Set aCell = Sheets("Sheet1").Cells.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        Set bCell = aCell
        aCell.Font.ColorIndex = 3
        aCell.Font.Bold = True
        Do While ExitLoop = False
            Set aCell = Sheets("Sheet1").Cells.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                aCell.Font.ColorIndex = 3
                aCell.Font.Bold = True
            Else
                ExitLoop = True
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
    End If
End Sub

Open in new window


Sid
0
 
gabrielPennybackAuthor Commented:
Hi Sid, thanks. This works great for coloring the entire cell, but I need something that only highlights "27D". I was going to post a comment but I couldn't get myself to stop trying to figure it out, and I managed to come with something surprisingly compact that works great.

I never would have gotten this except for your post, so thanks.

 - John
Sub FormatText()
Dim cel As Range, str As String
str = [G1].Value
For Each cel In Range("D8:D194")
If InStr(cel.Value, str) <> 0 Then
cel.Characters(Start:=InStr(cel.Value, str), Length:=3).Font.Bold = True
cel.Characters(Start:=InStr(cel.Value, str), Length:=3).Font.ColorIndex = [G1].Font.ColorIndex
cel.Offset(0, -3).Interior.ColorIndex = 45
End If
Next
End Sub

Open in new window

0
 
gabrielPennybackAuthor Commented:
I tried closing this 2 hours ago but I couldn't. Thanks again!

- John
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now