Multiple fonts in a single cell

[RachelMads02]

Hi guys, My name is Rachel. I was recently working on a workbook in another thread titled: Indexing formula needed for two pages at:

http://www.excelforum.com/excel-form...two-pages.html

I have a workbook attached below that has two sheets "Options" and "Cases". On the "Cases" sheet i have a column titled "Quality level" - column D. When i place the letters E,P,R,M, in any cell in column D they will place the corresponding item in Row 3 of the "options" sheet depending on the option choice. For example if i place the letter E in Quality Level cell D3 it will display in Options cell B3:D3.

If i place any combination of letters in any cell in Quality Column D they will reflect in the corresponding cells in Row 3 of the Options Sheet. I hope that's not too confusing.

Now what I'm looking to do and i don't even know if it's possible, is that I want the letters

E = Purple Font
P = Blue Font
R = Green Font
M = Orange Font

So that any time i place the letter E in any cell in column D it will always display as purple. The same for P,R, and M. Is that possible?

Thanks in advance to anyone who takes the time to help!!

[1aaaaaaaaaaaaaaa]

Hi, put this in the code module for Sheet2(Cases), and remove the conditional formatting from the Cases sheet. Adjust the RGB values as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Call Set_Character_Color

End Sub

Sub Set_Character_Color()
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("CASES")
    
    Dim nLR As Double
    
    nLR = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
    
    For x = 3 To nLR
        
        For y = 1 To Len(ws.Cells(x, 4))
        
            With ws.Cells(x, 4).Characters(Start:=y, Length:=1).Font
                
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "E" Then .Color = RGB(160, 32, 240)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "P" Then .Color = RGB(0, 0, 255)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "R" Then .Color = RGB(0, 255, 0)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "M" Then .Color = RGB(255, 153, 51)
                
            End With
            
            
        Next y
        
    Next x

End Sub

[RachelMads02]

Hi, put this in the code module for Sheet2(Cases), and remove the conditional formatting from the Cases sheet. Adjust the RGB values as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Call Set_Character_Color

End Sub

Sub Set_Character_Color()
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("CASES")
    
    Dim nLR As Double
    
    nLR = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
    
    For x = 3 To nLR
        
        For y = 1 To Len(ws.Cells(x, 4))
        
            With ws.Cells(x, 4).Characters(Start:=y, Length:=1).Font
                
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "E" Then .Color = RGB(160, 32, 240)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "P" Then .Color = RGB(0, 0, 255)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "R" Then .Color = RGB(0, 255, 0)
                If Mid(ws.Cells(x, 4).Value, y, 1) Like "M" Then .Color = RGB(255, 153, 51)
                
            End With
            
            
        Next y
        
    Next x

End Sub

This works!! Thank you. One more question. I'm going to have multiple different pages similar to "CASES". Do i simply copy the code you gave me into each sheet or are there additional changes i need to make?

[1aaaaaaaaaaaaaaa]

This works!! Thank you. One more question. I'm going to have multiple different pages similar to "CASES". Do i simply copy the code you gave me into each sheet or are there additional changes i need to make?

Yep! That will work, copy the code into each worksheet module. Make sure you change the worksheet reference (set ws = "sheet_name") also if the column changes, adjust the .cells reference.

[RachelMads02]

Okay I'll try it out. Thank you!

[walruseggman]

Another approach:

Private Sub Worksheet_Change(ByVal Target As Range)
Chars = Array("E", "P", "R", "M")
Clrs = Array(13, 5, 4, 46)
If Target.Column = 4 Then
    For i = 0 To UBound(Chars)
    x = InStr(Target.Value, Chars(i))
    Do While x > 0
    Target.Characters(x, 1).Font.ColorIndex = Clrs(i)
    x = InStr(x + 1, Target.Value, Chars(i))
    Loop
    Next
End If
End Sub

[RachelMads02]

I assume this [nLR = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row] is where i change the column letter if it changes in the other sheets?

[1aaaaaaaaaaaaaaa]

I assume this [nLR = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row] is where i change the column letter if it changes in the other sheets?

Right and ws.cells (x, 4) where 4 corresponds to the column number (A = 1, B = 2 etc.)

[RachelMads02]

is there anyway to speed up the process of the letters EPRM changing colors once imputed?

[walruseggman]

I believe the approach I posted would be faster for changing colors on newly inputted characters just because that's all it's looking at

[RachelMads02]

okay thank you!!