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

dbrice

New Member
Joined
Feb 15, 2017
Messages
9
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

dbrice

New Member
Joined
Feb 15, 2017
Messages
9
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:
Brodnik, J. -SPO MFF®

<tbody>
</tbody>

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:
NameWatchUnique IDRankSector / AssignmentR/SLastFirst In.Ordering
Lee-Quinn, M. -Sgt.Admin2398Sgt.B/FLee-QuinnM.2
Dodson, F. - Off. Admin5391Ofc.ADB/FDodsonF.3
Rice, D. - Off. Admin3945Ofc.W/MRiceD.3
Stafford, S. - Off.Admin4778Ofc.B/MStaffordJ.3
Sweet, S. TFO Admin1740Ofc.W/MSweetS.3
Toomer, D. - Off. Admin5458Ofc.B/MToomerD.3
Little, D. -Lt.BLVD0900Lt.W/MLittleD.1
Apple, T. -Sgt. MFFBLVD3757Sgt.S W/MAppleT.2
Strom, J. - Sgt. BLVD3753Sgt.W/MStromJ.2

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 15, 2017
Messages
9
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

New Member
Joined
Feb 15, 2017
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top