Excel Formula Colour Coding

Hello    all,

Lets assume I have a random letter in in the following cells; A1, A2 & A3.  In cell A4 I have a type of animal.  In cell B1 I simply have "=concatenate(A1,A2,A3,A4)".  

For example:

           A                              B                        
1         X                         XZVCat                                
2         Z
3         V
4        Cat

What I would like to do is have each part of the data in  the cell B1 colour coded.  I.e.  First letter Blue, second letter yellow, third letter green with the type of animal being black.

Is there anyway to achieve such an idea?

Many thanks, Alan.
Canders_12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
>> Is there anyway to achieve such an idea?

Sure, but how you define the colors?
0
Canders_12Author Commented:
Sorry,

First letter = Blue font
Second letter = Yellow font
Third = Green font
Animal = black font

So in the example above  XZVCat X would be in Blue, Z would be in Yellow, V would be in Green with the word cat being in black font.

Many thanks, Alan.
0
byundtMechanical EngineerCommented:
You can do what you want in VBA, but as far as I know it is impossible if value must be returned by a formula in the cell.

Are you open to replacing your formulas with a macro?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Canders_12Author Commented:
I am afraid a macro is not an option as it was going to be used for purchase orders that are typed in willy nilly and any vba would be just to unfriendly to the end user.

Oh well, something so simple seems so impossible lol
0
Patrick MatthewsCommented:
As Brad indicates, it would require a macro.  However, it need not be user-unfriendly.

For example, if this code is placed in the code module for the sheet used for data entry, then it will automatically run any time a user makes a change in A1:A4, so long as macros are enabled.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cel As Range
    
    If Not Intersect(Target, Me.[a1:a4]) Is Nothing Then
        Application.EnableEvents = False
        Set cel = Me.[b1]
        cel = Me.[a1] & Me.[a2] & Me.[a3] & Me.[a4]
        cel.Characters(1, 1).Font.Color = vbBlue
        cel.Characters(2, 1).Font.Color = vbYellow
        cel.Characters(3, 1).Font.Color = vbGreen
        cel.Characters(4, Len(cel)).Font.Color = vbBlack
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jppintoCommented:
Without a macro, you won't be able to do this unless the user makes it manually by selecting each letter and changing the font color, wich I don't think that you will want.

jppinto
0
Canders_12Author Commented:
matthewspatrick:

I appreciate the code snippet but the actual sheet will have hundreds of cells (not next to each other) that may contain purchase orders.

I appreciate everyones help but it looks like it is a no :(
0
byundtMechanical EngineerCommented:
Would highlighting work as well?

You could put the highlighting in the sheet background with a bit of fiddling. It won't print, but would certainly be visible on your purchase order form. VBA would not be required.
0
Canders_12Author Commented:
Mmmm,

Perhaps I am being too negative.  I tried your code above and got the following error:

Can't find project or library.

If I can get the code working then I could always say to change colour whenever a cell is update where its first letter begins with "P".  Any ideas with the error?
0
Patrick MatthewsCommented:
Canders_12,

First off, go to the VB Editor, and select Tools|References from the menu.  ANy listed as missing?

If not, then please upload a copy of your file, because it is working splendidly for me :)

Patrick
0
Canders_12Author Commented:
matthewspatrick:

I am not fully sure what you mean :/  My file is full of confidential prices.  Are you able to upload your sample file?

Sorry for being awkward.
0
Patrick MatthewsCommented:
Here you go:


Q-26938472.xls
0
Canders_12Author Commented:
Not sure what I was doing wrong but all good :)

I think I can take your idea and maybe, just maybe come up with an idea that will both give the solution and remove the hundreds of formulas the current sheet contains.

Many thanks, Alan.
0
Canders_12Author Commented:
PS

Many thanks to everyone else who also chipped in.
0
Patrick MatthewsCommented:
Glad to help :)
0
Canders_12Author Commented:
By using your suggestion and having the letter "P" in a row in column G that may contain an order it only ruddy works :)  And it actually reduces the need for so many long concatenate formulas that may never ever get used.  Many many thanks :)

Private Sub Worksheet_Change(ByVal Target As Range)
       
        If Range("G" & ActiveCell.row - 1) = "P" Then
       
            Application.EnableEvents = False
           
            ActiveCell.Offset(-2, 0).Value = "CONACTENE CELLS HERE"
           
            ActiveCell.Offset(-2, 0).Characters(1, 1).Font.Color = vbBlue
            ActiveCell.Offset(-2, 0).Characters(2, 1).Font.Color = vbYellow
            ActiveCell.Offset(-2, 0).Characters(3, 1).Font.Color = vbGreen
            ActiveCell.Offset(-2, 0).Characters(4, Len(cel)).Font.Color = vbBlack
            Application.EnableEvents = True

        End If
   
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.