Sometimes, you need to differentiate some parts of text in an Excel cell. This can be done using formatting those characters.
Here is the way that can be done using VBA using the Characters property
Sub Color_Part_of_Cell()
'Print ActiveCell.Characters.Count
With ActiveCell.Characters(2).Font
.Color = RGB(36, 182, 36)
End With
With ActiveCell.Characters(2, 2).Font
.Color = RGB(36, 182, 36)
End With
With ActiveCell.Characters(, 2).Font
.Color = RGB(36, 182, 36)
End With
End Sub
The output of the above is
Syntax
expression.Characters(Start, Length)
expression Required. An expression that returns an object in the Applies To list.
Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character.
Length Optional Variant. The number of characters to be returned. If this argument is omitted, this property returns the remainder of the string (everything after the Start character)
The same characters object can be used to make a character Bold / Italic
Posts
Hi
ReplyDeletecan you extract a text string from the excel cell, which has a different color? The text in the cells is in black and blue, but could not figure out how to make extraction of the blue or black work.
Thank you.
Hi
ReplyDeleteYou can try something similar to the following to extract colored text to a separate string
Sub Extract_Color_Part_of_Cell()
Dim sColoredText
For i1 = 1 To ActiveCell.Characters.Count
If (ActiveCell.Characters(i1, 1).Font.Color <> vbAutomatic) Then
sColoredText = sColoredText & ActiveCell.Characters(i1, 1).Text
End If
Next i1
End Sub
Hello,
ReplyDeleteCan this be modified to show a trigger instead of a certain character location. For example, if the name John is in the cell, then everything from John to the end of the string be another font color.
So glad I found this. You initial code and response post were exactly what I was looking for. Thanks!
ReplyDeleteThis will not work if the cell has a custom number format. Change to a non-custom format (eg General) and it works as expected.
ReplyDeletePaul Wright
www.excelexpert.com
Hello Shasur. This is what I need. The report template I'm working on has built in macros. I don't know how exactly to implement the codes you wrote. For example, how can I modify your code to point to a specific cell in my report? Also, when I insert your codes as part of a subset of an existing Sub and ran the macro, it tells me that it was expecting an End Sub. I'm clueless as to what to do since I'm very green at Excel VBA. Any clues you could provide would be greatly appreciated.
ReplyDeleteOk, but How can I do to write in a cell with different colors ?
ReplyDeleteHi,
ReplyDeleteIn a project that I am presently working on, I have multiple text values in 1 cell and depending on a pre defined condition, I need to colour code those different values - but all in 1 cell. I understand the syntax: With Cells(1, 2).Characters(Start:=ii, Length:=Len(ss)).Font .Color = -11489280
but the problem that I am facing is that regardless of what the condition instructs, once the next value enters the same cell with a different colour, the previous value changes it's colour to the same as the one just entered. Thus all the values have the same colour, except maybe the last value.
Please advise. Thanks.
You'll need to create "event handlers." You want to save the existing color scheme BEFORE the user types something new in the cell, and then re-apply the color scheme after he hits enter.
ReplyDeleteSuppose the cell you are interested in is Sheet2!E25. Open up visual basic, and on the top left side of the window you will see a list of all open workbooks. Each is named "VBAProject (SRMDataEntry)" where SRMDataEntry is the name of your workbook. If there is a plus sign next to your workbook, click on it to expand it. You will see a list of all the worksheets and all the modules in the SRMDataEntry workbook.
Find the spreadsheet that has the cell you want to keep colored in a certain way. Right click on that worksheet & select "view code." This is where you put event handler code.
Try playing around with two particular events -- worksheet_change and worksheet_selectionchange, like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
msgbox("You selected a new cell with your mouse or keyboard - and the cell you selected is " & target.address )
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
msgbox("You entered new data in cell " & target.address )
End Sub
once you get the hang of how events work, the solution to your problem starts to become clear (or at least clearer). Instead of popping up msgboxes, you automatically reapply your preferred formatting whenever the contents change in your special input cell. Here's an example:
ReplyDeletePrivate Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$b$27" then
target.Characters(1,5).font.color = rgb(255,0,0)
'sets 5 characters starting with first character to red
'i.e. sets characters 1-5 to red
target.Characters(6,5).font.color = rgb(0,255,0)
'sets 5 characters starting with 6th character to green
'i.e. sets characters 6-10 to green
target.Characters(11).font.color = rgb(0,255,0)
'sets ALL remaining characters starting with 11th character to blue
'i.e. sets characters 11, 12, 13 ... to blue
End if
End Sub
The only downside of the above is that it will crash if the user enters less than 11 characters. Better would be to create a hidden spreadsheet named "Secret", and put your "color template" in cell A1 in Secret. Then you can do as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$b$27" then
for i = 1 to min(target.characters.count, _
range("Secret!A1").characters.count)
target.Characters(i).font.color = _
range("Secret!A1").characters(i,1).font.color
next i
End if
End Sub
If the entry in B27 is longer than the entry in Secret!A1, then the last iteration of the loop will set target.characters(n).font.color to the color of the last character in Secret!A1. target.characters(n).font.color sets the color of ALL characters starting at n.
If you wanted to use the contents of E27 as your color scheme, rather than having a predetermined color scheme, you would use the Worksheet_SelectionChange event to tip off the program that someone has clicked on E27 -- BEFORE he makes any changes. Like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.address = "$E$27" then _
range("Secret!A1") = range("E27")
End Sub
Hope that helps.
I only changed 3 last characters in string
ReplyDeletethis is my code, but it now work(Excel 2007)
Sub ChangeColor()
Dim strContent As String, strTemp As String
Dim i As Integer, leng As Integer
strContent = Range("A" & 1).Value
leng = Len(strContent)
With ActiveCell.Characters(leng - 3, 1).Font
.Color = RGB(36, 182, 36)
End With
End Sub