• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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.
0
Canders_12
Asked:
Canders_12
  • 8
  • 4
  • 2
  • +2
1 Solution
 
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
 
byundtCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
byundtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now