asked on

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.

Jorge Paulino

>> Is there anyway to achieve such an idea?

Sure, but how you define the colors?

Canders_12

ASKER

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.

byundt

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?

Canders_12

ASKER

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

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

Canders_12

ASKER

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 :(

byundt

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.

Canders_12

ASKER

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?

Patrick Matthews

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

Canders_12

ASKER

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.

Patrick Matthews

Here you go:

Q-26938472.xls

Canders_12

ASKER

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.

Canders_12

ASKER

PS

Many thanks to everyone else who also chipped in.

Patrick Matthews

Glad to help :)

Canders_12

ASKER

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