Solved How do I change text color between certain characters

April 29, 2015 at 06:39:09
Specs: Windows 7
I have text that is typed between () to be blue and if text that is typed between {} to be purple and all other text would be black in a range of cells.

I'm sure it's done under conditional formatinng but I don't know how.


See More: How do I change text color between certain characters

Report •


✔ Best Answer
April 29, 2015 at 13:05:33
A few assumptions were made here:

1 - You used the phrase "range of cells". I assumed that the range is Column A. I'm probably wrong, but as written, that is where the code will look for the data.

2 - There are mulitple shades of blues and purples that can be chosen. I chose ColorIndex 5 as the Blue and ColorIndex 29 as the Purple. I can show you a quick way to determine the ColorIndex numbers for some other colors if you need me to.

3 - I assume that you do not have any instances of a set of parentheses contained within a set of braces or vice-versus. If you do, you may see inconsistant results. Let me know if that is an issue.

With that said, I belive that this code will accomplish your goal.

Option Explicit

Sub ColorMyWords()
Dim lastRw, nxtRw, nxtChr, nxtPara, nxtBrac
Dim paraCnt, bracCnt, openPara, closePara, openBrac, closeBrac

'Determine last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row

'Reset all text to black - Uncomment to use
  'Range("A1:A" & lastRw).Font.ColorIndex = xlAutomatic

'Loop through each cell in Column A
  For nxtRw = 1 To lastRw

'Determine number of Open Parenthesis and Open Braces in cell
   For nxtChr = 1 To Len(Range("A" & nxtRw))
      If Mid(Range("A" & nxtRw), nxtChr, 1) = "(" Then paraCnt = paraCnt + 1
      If Mid(Range("A" & nxtRw), nxtChr, 1) = "{" Then bracCnt = bracCnt + 1
   Next
  
'Find each set of Parentheses and color contained text Blue (5)
   For nxtPara = 1 To paraCnt
      openPara = InStr(openPara + 1, Range("A" & nxtRw), "(")
      closePara = InStr(closePara + 1, Range("A" & nxtRw), ")")
          Range("A" & nxtRw).Characters(openPara + 1, closePara - openPara - 1) _
                                        .Font.ColorIndex = 5
   Next
  
'Find each set of Braces and color contained text Purple (29)
   For nxtBrac = 1 To bracCnt
      openBrac = InStr(openBrac + 1, Range("A" & nxtRw), "{")
      closeBrac = InStr(closeBrac + 1, Range("A" & nxtRw), "}")
          Range("A" & nxtRw).Characters(openBrac + 1, closeBrac - openBrac - 1) _
                                        .Font.ColorIndex = 29
   Next
   
'Reset counting varibles for next cell
      paraCnt = 0
      openPara = 0
      closePara = 0
     
      bracCnt = 0
      openBrac = 0
      closeBrac = 0
  
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
April 29, 2015 at 08:41:34
I don't believe that it can be done with Conditional Formatting since Conditional Formatting acts upon the entire cell, not upon individual characters. In other words, it could look for the brackets and if they exist, format all of the text, but it can't format individual characters within the cell.

Obviously in can be done manually, but I don't think that that is how you would want to do it.

It could be done with a macro which would search the cells for the bracket sets ( ) & { } and set the font color for any characters contained within them.

Before any code could be offered, we would need to know more about the contents of the cells. For example, is there more than one occurance of the brackets within the same cell?

In other words, do you have this:

Tom is a (very) good boy.

or this:

Tom is a (very) good and (extremely) tall boy.

or this:

Tom is a (very) good and (extremely) tall {but uncoordinated} boy.

or something completely different?

The more details you can provide, the better we can assist you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
April 29, 2015 at 08:46:35
Within the text it could be more than one response between () or {}. Example

Tom is a (very) good and (extremely) tall {but uncoordinated} boy.


Report •

#3
April 29, 2015 at 08:53:15
So does that mean that you are open to a macro solution?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 29, 2015 at 08:54:48
yes open to a macro solution

Report •

#5
April 29, 2015 at 09:20:34
This will take some work, be patient. Ill try to work on it today, but no guarantees.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 29, 2015 at 13:05:33
✔ Best Answer
A few assumptions were made here:

1 - You used the phrase "range of cells". I assumed that the range is Column A. I'm probably wrong, but as written, that is where the code will look for the data.

2 - There are mulitple shades of blues and purples that can be chosen. I chose ColorIndex 5 as the Blue and ColorIndex 29 as the Purple. I can show you a quick way to determine the ColorIndex numbers for some other colors if you need me to.

3 - I assume that you do not have any instances of a set of parentheses contained within a set of braces or vice-versus. If you do, you may see inconsistant results. Let me know if that is an issue.

With that said, I belive that this code will accomplish your goal.

Option Explicit

Sub ColorMyWords()
Dim lastRw, nxtRw, nxtChr, nxtPara, nxtBrac
Dim paraCnt, bracCnt, openPara, closePara, openBrac, closeBrac

'Determine last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row

'Reset all text to black - Uncomment to use
  'Range("A1:A" & lastRw).Font.ColorIndex = xlAutomatic

'Loop through each cell in Column A
  For nxtRw = 1 To lastRw

'Determine number of Open Parenthesis and Open Braces in cell
   For nxtChr = 1 To Len(Range("A" & nxtRw))
      If Mid(Range("A" & nxtRw), nxtChr, 1) = "(" Then paraCnt = paraCnt + 1
      If Mid(Range("A" & nxtRw), nxtChr, 1) = "{" Then bracCnt = bracCnt + 1
   Next
  
'Find each set of Parentheses and color contained text Blue (5)
   For nxtPara = 1 To paraCnt
      openPara = InStr(openPara + 1, Range("A" & nxtRw), "(")
      closePara = InStr(closePara + 1, Range("A" & nxtRw), ")")
          Range("A" & nxtRw).Characters(openPara + 1, closePara - openPara - 1) _
                                        .Font.ColorIndex = 5
   Next
  
'Find each set of Braces and color contained text Purple (29)
   For nxtBrac = 1 To bracCnt
      openBrac = InStr(openBrac + 1, Range("A" & nxtRw), "{")
      closeBrac = InStr(closeBrac + 1, Range("A" & nxtRw), "}")
          Range("A" & nxtRw).Characters(openBrac + 1, closeBrac - openBrac - 1) _
                                        .Font.ColorIndex = 29
   Next
   
'Reset counting varibles for next cell
      paraCnt = 0
      openPara = 0
      closePara = 0
     
      bracCnt = 0
      openBrac = 0
      closeBrac = 0
  
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#7
May 6, 2015 at 01:38:06
Hi Derby,

This once again this so awesome, as always i like to look at your code to learn what i can from it and see how it works.

One thing i noticed is that with the code is that the last parenthesis and last brace would also be coloured alaong with the text.

I made small change by adding a ' - 1' after openPara, (for some reason i couldnt bold this in my post). hope you dont mind me messing with your code, this will exclude the last brace and parenthesis and only colour the text

Range("A" & nxtRw).Characters(openPara + 1, closePara - openPara - 1) _
                                        .Font.ColorIndex = 5

once again brilliant code mate im going to save this as i am sure it will be useful.

message edited by AlwaysWillingToLearn


Report •

#8
May 6, 2015 at 06:55:33
You are correct...my old eyes didn't catch the color change of the brackets. Thanks for fixing the code. I have edited my post to reflect your change. Of course it may not matter since we haven't heard back from the OP. No way to tell if (s)he is still looking for a solution.

re: for some reason i couldnt bold this in my post

The reason is based on the limitations of the editor used in this forum. Once the pre tags are applied to a section of text, no other text formatting can be applied to that section.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question