Articles

Change color to a portion of a text in excel

September 17, 2010 at 18:13:26
Specs: Microsoft Windows XP Professional, 2.592 GHz / 2047 MB

This might sound silly but i need help with this one, i have a spreedshet that in a cell contains information and i will like to have part of it in a font color and the other part in a different font color for example: my dog is happy. i want the my dog be in deafult black and is happy in a color red

See More: Change color to a portion of a text in excel

Report •


#1
September 17, 2010 at 20:20:36

Hi,

Either select the cell and use in-cell editing to select "is happy"
or select the cell and edit in the formula bar and select the text "is happy"
Then right-click the selected text and select 'Format cells...'
Format the selected text with the required color.

The unselected text will retain the default color or any color it was previously formatted with.

Regards


Report •

#2
September 17, 2010 at 20:23:59

Do you think is possible to have something more easier since I preatty much will need to do the same for several cells

Report •

#3
September 17, 2010 at 20:31:40

You can't change text color with a formula, so it's either manually as Humar suggested or with VBA.

Any VBA code would need to know how to determine which characters to "color" either via a count of characters or words or something to key on.

Keep in mind that Excel was designed to work with numbers and isn't well suited for doing fancy stuff with text.


Report •

Related Solutions

#4
September 17, 2010 at 20:37:17

Can someone help then to do it in VBA since really im not good at all

Report •

#5
September 18, 2010 at 06:09:27

As I said in my first response:

"Any VBA code would need to know how to determine which characters to "color" either via a count of characters or words or something to key on."

For your specific example, this would work because "is happy" starts at character 8 and the string is 9 characters long. ColorIndex 3 is Red.

Sub RedIsHappy()
    Range("A1").Characters(Start:=8, Length:=9).Font.ColorIndex = 3
End Sub

You'll notice that I had to tell VBA exactly where to start setting the font color.

There are various ways for VBA to determine the start character and length on it's own by using variables, but you still have to give it the criteria to work on.

For example, this will work for your example by asking the user where to start and then setting the color of everything from that character number on.

Sub RedIsHappy()
'Get input from user
 chrNum = _
  Application.InputBox("Enter the number of the" & _
      "first character to be Red:", "Color My Words")
'Set font color
  Range("A1").Characters(Start:=chrNum, _
    Length:=Len(Range("A1"))).Font.ColorIndex = 3
End Sub

You can expand on this concept and not only pass a starting number but also a specific number of characters so you can set the font color in the middle (or beginning) of a string.

You could even get really fancy and write code to find specific words (by word, not by character number) to color, but that gets increasingly complicated, especially if the word you want to color appears more than once but you don't *all* of them colored.

Bottom line is that if you have strings of various lengths and you want to set the font color of different parts of each string, you have to set the code to loop and then tell VBA what to do, cell by cell.


Report •

#6
September 20, 2010 at 12:29:32

Great thanks for your assistance yet this will work as if the cell contains a formula like this : ='Calc'!C14&IF(A33=TRUE,IF($A$40=$R$35," /// "&(TEXT($C$41,"hh:mm")&" Tom request to move "&A38&" agents from "&A40&" to "&B40),IF($B$40=$R$35," /// "&(TEXT($C$41,"hh:mm")&" NRP request to move "&A38&" agents from "&A40&" to "&B40),"")),"")&IF(A32=TRUE," /// "&(TEXT($C$41,"hh:mm")&" Tom change color code from "&A36&" to "&B36),"") in which I want to have in red the text that would come as a end result of the formula after 'Calc'!C14, is that possible?

Report •

#7
September 20, 2010 at 13:43:58

VBA can not change the font color of a portion of text that is the result of a formula since VBA will see the contents of the cell as a formula and not perform a "text operation" on it.

You could set the font color of the entire cell to be red via VBA, but not just certain characters.

A workaround is to place your formula in a hidden cell and then use a Worksheet_Change macro to copy the value that the formula produces into a visable cell and then set the text color of characters in that cell.

I'm not going to try and reproduce your formula, so let's use this example:

Let's say:

C14 contains My dog
C1 contains is
D1 contains happy

In B10 I'll place this formula and then Hide the row:

=Calc!C14 & IF(B1="A"," " & C1 & " " & D1, "")

When B1 contains A, B10 will display:

My dog is happy

Since I can't format the text in that cell because the cell really contains a formula, I'll use the following code to place the value returned by that formula into B11.

B11 will now contain the text My dog is happy

Whenever any change is made to the sheet, this code will "copy" the result of B10 into B11, determine the length of C14 (LEN("My dog" = 5), add 1 to it and use that value as the Start argument to set all characters from there to the end of the string to be red.

The final result in B11 will be:

My dog is happy

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chrNum As Integer
'Copy B10 to B11
   Range("B11") = Sheets(1).Range("B10").Value
'Get start number from C14
   chrNum = Len(Range("C14")) + 1
'Set font color of characters in B11
   Range("B11").Characters(Start:=chrNum, _
    Length:=Len(Range("B11"))).Font.ColorIndex = 3
End Sub


Report •


Ask Question