Does Conditional Formatting apply to each Character in a Cell?

e4excel · #1 12-05-2009, 09:37 AM

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

**teylyn** · #2 12-05-2009, 06:24 PM

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

e4excel · #3 12-06-2009, 02:20 PM

Quote:

Originally Posted by teylyn

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

I have formatted individually to "purple" colour so when there's 1 in F1 "A" remains the same i.e Purple however when I cahnge the value F1=2 then only "A" changes to Blue while rest of the Letters BCDE reamin unchanged..

Quote:

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.

Is there a way of overriding the same or an you suggest a workaround...

**teylyn** · #4 12-06-2009, 02:34 PM

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

e4excel · #5 12-06-2009, 02:58 PM

Ok thanks a lot Tevelyn got my answer that its not possible in Excel as I was unsure about that...!

However if were to involve VBA , can you please add an attachment for the same..

I had though of bifurcating the characters in Multiple cells but as per the existing design of the present workbook it won't be feasible..

Would appreciate if you could come up with something via VBA which is not my CUp of Tea at all..

Please provide explantions wherever neccessary as I do not get VBA very easily but off late have started using it more frequently with the Forum's help...

Thanks a lot for the confirmation ANyways..

**teylyn** · #6 12-06-2009, 03:04 PM

Quote:

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

**mikerickson** · #7 12-07-2009, 01:26 AM

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

Code:

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

e4excel · #8 12-07-2009, 04:28 AM

Dear Mick,

I have tried using your code in my file but I am not sure whether am I using it correctly as Im no good in VBA.

Nothings happening as in there's nothing new happening..
I wil attach the file you can check and let me know...

Thanks..

pike · #9 12-07-2009, 04:57 AM

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)

e4excel · #10 12-07-2009, 08:41 AM

Dear Pike,

Thanks for the update, I knew that I was doing something wrong but am not good enough to even know that in VBA..
However, though its working it turns completely blue after I enter 2 in A2 and does not show any text in B2..Please tell me what am i supposed to do about that?

Just had a very quicjk question how many colours are there in Excel available to format..?

**teylyn** · #11 12-07-2009, 02:59 PM

Quote:

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.

e4excel · #12 12-08-2009, 12:27 AM

Quote:

Originally Posted by teylyn

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

Thanks Tevelyn,

I was not sure about the range, anyways I use 2003 I would be limited to 56..

Dear Forum,

If someone can check the attachemnt and lemme know as to what changes I need to make then it would be great..!

Regards

**mikerickson** · #13 12-08-2009, 09:22 AM

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

darkyam · #14 12-08-2009, 11:35 AM

Quote:

Originally Posted by teylyn

Gazillions in 2007.

Technically, only 16,777,216. Each of the three main colors has 256 shades recognized, so it's 256^3. Don't really know why they have so many, though. I think it's one of those changes they made more because they could than because people demanded millions of colors.

Welcome to the Excel Forum

Forum Statistics