Colour coding text in Excel

cwoodham · 10 February 2006 at 8:19PM

I wonder if anyone can help on this. I'm trying to colour code text in a column so that when I enter say a three letter text string in a cell Excel automatically gives it a predefined colour.
I've tried conditional formatting and can get three colours this way plus the plain black for text which is not formatted, i.e. four colours in total. But I need up to 10 colours.
Is there any way to do this? Maybe there's an online resource which explains it. I tried a Google search and couldn't find anything. I'm little more than a novice with Excel so please explain in simple terms.
Thanks in advance.

jonnyb · 10 February 2006 at 8:27PM

just looked at my excel and conditional formatting allows me to use the whole colour palette.
what version excel you got ? I have 2000.

AndyRat · 10 February 2006 at 8:29PM

I seem to remember away of doing this advertised on the ozgrid.com website a couple of months back

HTH

jonnyb · 10 February 2006 at 8:33PM

sorry - just checked again. I realise the limitation is that excel only allows each cell to have up to 3 conditions set.

cwoodham · 10 February 2006 at 8:34PM

johnnyb, I've Excel 2000. I can select from the whole palette okay, its just that conditional formatting only seems to allow three 'picks' from the palette.

cwoodham · 10 February 2006 at 9:20PM

AndyRat: Thanks. I went to the ozgrid.com website and got some help here

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

I copied the VBA code into the codes window of the worksheet as instructed and put in the text I wanted instead of Case and I was able to change the cell background colours conditionally, which is a good result!
I guess if I knew more about VBA I could change the font colours but this will do in the meantime.

EvilMonkey · 10 February 2006 at 9:28PM

You're nearly there, a quick search on google for "Excel VBA font colour" returned a number of pages, the second one said this...

"ActiveCell.Font.Color = RGB(255,0,0)
sets the active cells font colour to Red (255 red, 0 green, 0 blue). Alternatively, you can use the ColorIndex property. This lets you specify an item in the workbooks palette (this can be set in the Color tab of the Excel Options dialog).

ActiveCell.Font.ColorIndex = 16

sets the font colour to the 16th item on the colour palette."

So you're looking at replacing interior.colorIndex with font.colorIndex

(or you can use RGB values with the first string)

Hope this sorts your problem

E.M.

Chippy_Minton · 10 February 2006 at 10:17PM

cwoodham wrote:

I guess if I knew more about VBA I could change the font colours but this will do in the meantime.

The best way of finding out how to perform an action in VBA is by using the macro recorder (Tools - Macro - Record New Macro). With the recorder running, perform the action as normal via the keyboard, mouse and menus and then click the 'Stop recording' button and open the VBA editor to see the results. The code can be a bit bloated and not the most efficient, but it's a good starting point.

Colour coding text in Excel

Comments

Confirm your email address to Create Threads and Reply

🚀 Getting Started

Categories

Is this how you want to be seen?

Get our FREE Weekly email full of deals & guides - and it’s spam-free