CODEWITHSUNDEEP
stringvbaexcelexcel-formulaexcel-2010
user2642587
user2642587

Reputation: 45

Search text string for a match and change font color

It's been 6 years since I've worked with Excel and i'm a little bit rusty. Here's my scenario:

I am exporting a list of issues to Excel. I need to be able differentiate the associated Link numbers in a cell (mulitple values) from each other. Example, i have two columns,

Key = the number for a ticket

Linked Issues = The Keys associated

I need a statement that would scan the Key column and find a match in the Linked Issues column. Then once the match is found the matching text will assume the font color of the Key.

Where this get complicated is each cell of the Linked Issues column could look something like this iss-3913, iss-3923, iss-1649. So essentially the scan would be for a match within the string. Any help is appreciated.

Upvotes: 2

Views: 4971

Answers (2)

user2642587
user2642587

Reputation: 45

This is an old post but I thought I would provide my work around to the conditional formating issue I was having.

Sub colorkey()
start_row = 5
key_col = 2
flag_col = 4

i = start_row 'start on row one

  Do While Not IsEmpty(Cells(i, key_col)) 'Do until empty cell

  Tval = Cells(i, flag_col).Value
    Select Case Tval
    Case "Requirement"
        'cval = green
        cVal = 10
    Case "New Feature"
        'cval = orange
        cVal = 46
    Case "Test"
        'cval = lt blue
        cVal = 28
    Case "Epic"
        'cval = maroon
        cVal = 30
    Case "Story"
        'cval = dk blue
        cVal = 49
    Case "Theme"
        'cval = grey
        cVal = 48
    Case "Bug"
        'cval = red
        cVal = 3
    Case "NOT MAPPED"
        'cval = Maroon
        cVal = 1
    End Select


Cells(i, key_col).Font.ColorIndex = cVal

    i = i + 1 'increment the cell in the first column
    Loop

End Sub
Sub colorlinked()
start_row = 5
key_col = 2
linked_col = 26
i = start_row 'start on row one
Do While Not IsEmpty(Cells(i, key_col)) 'Do until empty cell
    o = start_row 'start with row one for second column
    Do While Not IsEmpty(Cells(o, linked_col)) 'Do until empty cell
    If Not InStr(1, Cells(o, linked_col), Cells(i, key_col)) = 0 Then  'if cell contents found in cell
        With Cells(o, linked_col).Characters(Start:=InStr(1, Cells(o, linked_col), Cells(i, key_col)), Length:=Len(Cells(i, key_col))).Font
            .Color = Cells(i, key_col).Font.Color  'change color of this part of the cell
        End With
    End If
    o = o + 1 'increment the cell in second column
    Loop
    i = i + 1 'increment the cell in the first column
Loop
MsgBox "Finished Scanning"
End Sub

Upvotes: 1

MakeCents
MakeCents

Reputation: 764

I am sorry, I don't have time to finish this right now, but wWould something like this help with maybe a loop for each cell in the first column?

Edit: Finished now, second edit to update to B5 and Z5, edit 3 fixed goof with column reference and updated to use variables to assign what column to look in.

Sub colortext()
start_row = 5
key_col = 2
linked_col = 26
i = start_row 'start on row one
Do While Not IsEmpty(Cells(i, key_col)) 'Do until empty cell
    o = start_row 'start with row one for second column
    Do While Not IsEmpty(Cells(o, linked_col)) 'Do until empty cell
    If Not InStr(1, Cells(o, linked_col), Cells(i, key_col)) = 0 Then  'if cell contents found in cell
        With Cells(o, linked_col).Characters(Start:=InStr(1, Cells(o, linked_col), Cells(i, key_col)), Length:=Len(Cells(i, key_col))).Font
            .Color = Cells(i, key_col).Font.Color  'change color of this part of the cell
        End With
    End If
    o = o + 1 'increment the cell in second column
    Loop
    i = i + 1 'increment the cell in the first column
Loop
End Sub

or maybe

Something like this?

Excel VBA: change font color for specific char in a cell range

Upvotes: 1

Related Questions