Thread: Does Conditional Formatting apply to each Character in a Cell?

Dear Forum,

I am trying to do a very simple Task of employing conditional Formatting in a Cell $B$2 which contains the following letters i.e characters.. [ A B C D E ]

They are coloured as follows :

A B C D E and this cell is filled with BLACK Fill Colour

Now If I enter 1 in the Cell $A$2, then the same colour scheme should remain in the Cell $B$2 but if I enter 2 then the formatting should change to BLUE FIll Colour and the Text to Yellow...

But when I tried changing the value in the Cell A2 to 2 , then only the first letter i.e "A" changes to Yellow Colour while rest does not change its colour i.e. remain the same...

Why is the entire cell B2 not getting formatted with all the contents?
And what is the remedy for the same?

Warm Regards
e4excel

I assume that A is formatted with the default cell's formatting, and the other letters are formatted individually in the formula editing box.

Conditional formatting will only cange the default format of a cell, so the individual color formats you have set manually for the other letters will override CF.

hth

Unless you want to involve VBA, there is probably no native Excel solution for this.

My suggestion is to split the different colored elements out into separate columns, use the default formatting to format each cell and apply conditional formatting to change it.

hth

can you please add an attachment for the same

Sorry, my VBA is too basic to do stuff like that. Maybe start a new thread in the programming forum and explain that you want to programatically format parts of a text string based on conditions.

hth

You could put this in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim i As Long
    If Not Application.Intersect(Target, Range("A2:B2")) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With Range("B2")
            If Val(CStr(Range("A2").Value)) <> 2 Then
                For i = 1 To .Characters.Count
                    With .Characters(i, 1)
                        Select Case .Text
                            Case "A"
                                .Font.Bold = True
                                .Font.ColorIndex = 54
                            Case "B"
                                .Font.Bold = True
                                .Font.ColorIndex = 3
                            Case "C"
                                .Font.Bold = True
                                .Font.ColorIndex = 44
                            Case "D"
                                .Font.Bold = True
                                .Font.ColorIndex = 13
                            Case "E"
                                .Font.Bold = True
                                .Font.ColorIndex = 14
                            Case Else
                                .Font.Bold = False
                                .Font.ColorIndex = xlAutomatic
                        End Select
                    End With
                Next i
                .Interior.ColorIndex = 1
            Else
                For i = 1 To .Characters.Count
                    With .Characters(i, 1)
                        .Font.Bold = False
                        .Font.ColorIndex = 6
                    End With
                Next i
                .Interior.ColorIndex = 5
            End If
        End With
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

mike is off line but
place the code in the "VBAProject" "Microsoft Excel Objects" "sheet1"
click on the work sheet1 and place the code
(same as you have done in module1)

how many colours are there in Excel available to format

56 in Excel 2003, but you can change the default color palette. Gazillions in 2007.

Putting the code in the Sheet1 code module and removing the CF from B2 should fix things.