How do I change the color of the number in a concatenated text string to a different color?

[dbrice]

For instance with:
=CONCATENATE(COUNTIF($H$17:$I$50, "*SPO*")+COUNTIF($H$17:$I$50, "*Officer *"), " Enforcement Total")

How do I make it come out with the count in red but the "Enforcement Total" be in black?

 

[Rick Rothstein]

For instance with:
=CONCATENATE(COUNTIF($H$17:$I$50, "*SPO*")+COUNTIF($H$17:$I$50, "*Officer *"), " Enforcement Total")

How do I make it come out with the count in red but the "Enforcement Total" be in black?

You can't... the only cells where parts of the text can have different formats is where those cells contain text constants, not formulas. You can do what you want using VBA event code to replace your formulas though... let us know if you want to pursue the VBA method or not.

 

[dbrice]

Let me ask a different question along the same lines of what you were saying. I am trying to apply color to text that is generated by formulas using VBA. But it is applying the color to the whole cell and not to just the selected text (in this case "SPO"):
Sub Apply_Color_To_Text2()


' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1


' Loop Through Rows 2 to 5
For Col = 1 To 150
For Row = 1 To 150


' Get Text in Current Cell
CurrentCellText = ActiveSheet.Cells(Row, Col).Value


' Get the Position of the Text SPO
SPOStartPosition = InStr(1, CurrentCellText, "SPO ")

' Colour the Word SPO Red
If SPOStartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(SPOStartPosition, 4).Font.Color = RGB(0, 112, 192)
End If


Next Row
Next Col
' Change color in specific cell
'ActiveSheet.Cells(8, 58).Characters(0, 2).Font.Color = RGB(255, 0, 0)


End Sub

I would like to apply coloring to text like this:
[TABLE="width: 175"]
<tbody>[TR]
[TD="class: xl65, width: 175"]Brodnik, J. -SPO MFF®[/TD]
[/TR]
</tbody>[/TABLE]

and this

Wessels, M. -Sgt.FMLA


Perhaps a different approach. Maybe a VBA function that finds a matching cell in the list of officers on sheet 3 (which is text and can be formated as wanted) and copy's the formating of that duplicate valued cell to the cell found in the formula generated sheet (Sheet 1).

Sheet 3 looks something like this:
[TABLE="width: 776"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Watch[/TD]
[TD]Unique ID[/TD]
[TD]Rank[/TD]
[TD]Sector / Assignment[/TD]
[TD]R/S[/TD]
[TD]Last[/TD]
[TD]First In.[/TD]
[TD]Ordering[/TD]
[/TR]
[TR]
[TD]Lee-Quinn, M. -Sgt.[/TD]
[TD]Admin[/TD]
[TD]2398[/TD]
[TD]Sgt.[/TD]
[TD][/TD]
[TD]B/F[/TD]
[TD]Lee-Quinn[/TD]
[TD]M.[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Dodson, F. - Off. [/TD]
[TD]Admin[/TD]
[TD]5391[/TD]
[TD]Ofc.[/TD]
[TD]AD[/TD]
[TD]B/F[/TD]
[TD]Dodson[/TD]
[TD]F.[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Rice, D. - Off. [/TD]
[TD]Admin[/TD]
[TD]3945[/TD]
[TD]Ofc.[/TD]
[TD][/TD]
[TD]W/M[/TD]
[TD]Rice[/TD]
[TD]D.[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Stafford, S. - Off.[/TD]
[TD]Admin[/TD]
[TD]4778[/TD]
[TD]Ofc.[/TD]
[TD][/TD]
[TD]B/M[/TD]
[TD]Stafford[/TD]
[TD]J.[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sweet, S. TFO [/TD]
[TD]Admin[/TD]
[TD]1740[/TD]
[TD]Ofc.[/TD]
[TD][/TD]
[TD]W/M[/TD]
[TD]Sweet[/TD]
[TD]S.[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Toomer, D. - Off. [/TD]
[TD]Admin[/TD]
[TD]5458[/TD]
[TD]Ofc.[/TD]
[TD][/TD]
[TD]B/M[/TD]
[TD]Toomer[/TD]
[TD]D.[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Little, D. -Lt.[/TD]
[TD]BLVD[/TD]
[TD]0900[/TD]
[TD]Lt.[/TD]
[TD][/TD]
[TD]W/M[/TD]
[TD]Little[/TD]
[TD]D.[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Apple, T. -Sgt. MFF[/TD]
[TD]BLVD[/TD]
[TD]3757[/TD]
[TD]Sgt.[/TD]
[TD]S[/TD]
[TD] W/M[/TD]
[TD]Apple[/TD]
[TD]T.[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Strom, J. - Sgt. [/TD]
[TD]BLVD[/TD]
[TD]3753[/TD]
[TD]Sgt.[/TD]
[TD][/TD]
[TD]W/M[/TD]
[TD]Strom[/TD]
[TD]J.[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 contains a formula like this:
=INDEX('Officer List'!$A:$I,MATCH("Sgt.",INDIRECT("'Officer List'!D"&Zone_6_Organizational_Chart!$C$8&":D300"),0)+Zone_6_Organizational_Chart!$C$8-1,1)
Where Zone_6_Organizational_Chart!$C$8 contains =MATCH("Admin",'Officer List'!B:B, 0) // which is the first match of the Watch equaling "Admin"

 

[Rick Rothstein]

Let me ask a different question along the same lines of what you were saying. I am trying to apply color to text that is generated by formulas using VBA. But it is applying the color to the whole cell and not to just the selected text (in this case "SPO"):

Perhaps a different approach. Maybe a VBA function that finds a matching cell in the list of officers on sheet 3 (which is text and can be formated as wanted) and copy's the formating of that duplicate valued cell to the cell found in the formula generated sheet (Sheet 1).

The text produced by a formula will always have the same format for every character in the text, individual characters can never have a different format from other characters within the text produced by a formula... there is no way to change this. There is a VBA workaround which involves removing the formula from the cell(s) and using event code to monitor changes in cells that were referenced by the original formula to place text constants in the cells and then apply the desired format to the individual characters, but this can sometimes require some tricky programming, especially if the original formula was complex.

 

[dbrice]

So... I could have the formulas in one sheet, then create a macro to copy/paste values to a different sheet and then apply the color to individual text characters, right? Would the VBA code be the same as above if it applied to a sheet with Text only? Would it color selected segments of text and not color the rest within the cells?

 

[dbrice]

I got it to work!
I copied the sheet with formulas (Lets call it "UnformattedSheet") to another sheet (the one that I want to apply color to / "ColorSheet").
Then I created a macro that copies the cells from "UnformattedSheet" to "ColorSheet" then copies those same cells from "ColorSheet" and pastes values in "ColorSheet" over the first paste.
Then it applies the colors I to the specified texts.
See below:

Sub Update_and_Color()
' This macro button is placed on the ColorSheet, and the UnformattedSheet (containing formulas) is made hidden.
' Update_and_Color Macro
' Copy's the unformatted Organizational Chart & pastes it to the Zone 6 Org Chart and then applies color to specified texts.
'


'
Sheets("Zone_6_Organizational_Chart_Unf").Select
Range("A1:Y73").Select
Selection.Copy
Sheets("Zone_6_Organizational_Chart").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C8").Select
Application.CutCopyMode = False
Range("K7").Select

'Apply Coloring
' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 2


' Loop Through Desired Rows
For Col = 2 To 26
For Row = 3 To 61


' Get Text in Current Cell
CurrentCellText = ActiveSheet.Cells(Row, Col).Value


' 1 Get the Position of the Text "SPO"
StartPosition = InStr(1, CurrentCellText, "SPO")
' Colour the Word SPO Red
If StartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Color = RGB(255, 0, 0)
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Bold = True
End If
' 2 Get the Position of the Text "TFO"
StartPosition = InStr(1, CurrentCellText, "TFO")
' Colour the Word TFO Red
If StartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Color = RGB(255, 0, 0)
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Bold = True
End If
' 3 Get the Position of the Text "FMLA"
StartPosition = InStr(1, CurrentCellText, "FMLA")
' Colour the Word FMLA Purple
If StartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 4).Font.Color = RGB(112, 48, 160)
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 4).Font.Bold = True
End If
' 4 Get the Position of the Text "®"
StartPosition = InStr(1, CurrentCellText, "®")
' Colour the Word ® Red
If StartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 1).Font.Color = RGB(255, 0, 0)
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 1).Font.Bold = True
End If
' 5 Get the Position of the Text "MFF"
StartPosition = InStr(1, CurrentCellText, "MFF")
' Colour the Word MFF Green
If StartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Color = RGB(0, 128, 0)
ActiveSheet.Cells(Row, Col).Characters(StartPosition, 3).Font.Bold = True
End If


Next Row
Next Col


End Sub