Link to home
Start Free TrialLog in
Avatar of tindavid
tindavid

asked on

excel vb to set background color of a cell text , only to text between 2 delimiter (say, [ xxxxxx ] )

As stated in the title given a cell value is of " /a/b/c/d/1/2/3/4 [ /2/3/1/ .....] /x/z/v/ ", how can I only set the background color of [ /2/3/1/ .....] to red and /a/b/c/d/1/2/3/4          /x/z/v  to Yellow  , in this case the delimiter is [,  however the delimiter can be other special characters such as { .
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

You may try something like this.....
Sub SetSpecificTextColor()
Dim lr As Long
Dim rng As Range, cell As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row     'Finds the last row used in col. A
Set rng = Range("A2:A" & lr)        'Assuming the data starts from row2 in col. A

For Each cell In rng
    If InStr(cell, "[") > 0 And InStr(cell, "]") > 0 Then
        cell.Font.Color = vbYellow
        cell.Characters(InStr(cell, "["), InStr(cell, "]") - InStr(cell, "[") + 1).Font.Color = vbRed
    ElseIf InStr(cell, "{") > 0 And InStr(cell, "}") > 0 Then
        cell.Font.Color = vbYellow
        cell.Characters(InStr(cell, "{"), InStr(cell, "}") - InStr(cell, "{") + 1).Font.Color = vbRed
    Else
        cell.Font.Color = vbBlack
    End If
Next cell
End Sub

Open in new window

Avatar of tindavid
tindavid

ASKER

It is the background color that I am looking for, not the color of the text.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That is exactly I am looking for .  Different portion of the text in the cell shall have different background colors.

Upload a sample workbook is not necessary as my request is simple.

I have also  notice that excel won't allow me to change portion of background color for a cell text.

 If you have no other channel to get the answer.  I may have to tell my client that this feature is not supported .

Administrator,  Please help to delete this question.

Thank you,
Yes that is not possible. As you can see that you cannot perform this action manually.

And also you cannot request to delete the question because as per the forum rules, if an answer is not possible for what you have asked in your question and if an expert has told you that it is not possible to achieve, in that case that will be accepted as an answer.

So in this case you may close the question by accepting one of my reply as an answer to your question.
I need to wait, may be someone elese can have a solution to it.