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 { .
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
        cell.Font.Color = vbBlack
    End If
Next cell
End Sub

Open in new window

tindavidAuthor Commented:
It is the background color that I am looking for, not the color of the text.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you mean two background colors into a single cell based on the specific text i.e. one background color for one portion of the string and another color for another portion of the string in the same cell?

I don't think that is possible. At least I haven't seen any such example.
Or am I missing something?

You can change the cell background depending on if the cell contains a specific string into it but you cannot have two different background colors based on two different text string in the same cell.

Would be helpful, if you upload a sample workbook and and show the desired output manually to let me know that what exactly you are trying to achieve.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

tindavidAuthor Commented:
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,
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
tindavidAuthor Commented:
I need to wait, may be someone elese can have a solution to it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.