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:
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:
Name | Watch | Unique ID | Rank | Sector / Assignment | R/S | Last | First In. | Ordering |
Lee-Quinn, M. -Sgt. | Admin | 2398 | Sgt. | | B/F | Lee-Quinn | M. | 2 |
Dodson, F. - Off. | Admin | 5391 | Ofc. | AD | B/F | Dodson | F. | 3 |
Rice, D. - Off. | Admin | 3945 | Ofc. | | W/M | Rice | D. | 3 |
Stafford, S. - Off. | Admin | 4778 | Ofc. | | B/M | Stafford | J. | 3 |
Sweet, S. TFO | Admin | 1740 | Ofc. | | W/M | Sweet | S. | 3 |
Toomer, D. - Off. | Admin | 5458 | Ofc. | | B/M | Toomer | D. | 3 |
Little, D. -Lt. | BLVD | 0900 | Lt. | | W/M | Little | D. | 1 |
Apple, T. -Sgt. MFF | BLVD | 3757 | Sgt. | S | W/M | Apple | T. | 2 |
Strom, J. - Sgt. | BLVD | 3753 | Sgt. | | W/M | Strom | J. | 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"