Copying Font from cell issues

[kat44]

Hi, first post here!

What I am trying to do is this:

I have a sheet where I have initials of people in one column. These can change to whatever I put in the cell.

What I want to do is if the initials are 'KO', 'BD' or 'WB' I want them to appear in red font (not cell colour). I want to do the same for 8 more initials of different colours (3 sets in Yellow, 3 sets in Green and 2 sets in Blue).

There are too many options here I think for conditional formatting (using Excel 2003). I have tried putting in some formula to copy the contents of another cell depending on the initials entered but again I can't do it.

Are there just too many options to be able to automatically change the colour of the text depending on the cell contents themselves please?

thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are using Excel 2003 and want to more than 4 different formats, you would need to use vba. There is quite a good explanation and example here

Post back with more details if you need further help with this.

 

[kat44]

Thank you.

That does help but what I really want to do is change the font colour, not the background colour.

So, if the code is true, I want the text (initials) to change to red/green etc and the cell background to stay clear.

Is that possible at all?

Thanks again

 

[wesimmo]

You can edit the code that was linked to to change the text colour and not the Cell.Interior.ColorIndex

e.g. Cell.Font.Color = -16776961 is what i just recorded when changing the cell text colour to red.

For ease, record changing the text colours to get the colour codes you need to put in the code (i.e. -16776961 = red on my palette)

 

[kat44]

Thank you.

Wasn't sure if it was the word 'font' you could change easy enough but it works!!

Thanks again

 

[kat44]

You can edit the code that was linked to to change the text colour and not the Cell.Interior.ColorIndex

e.g. Cell.Font.Color = -16776961 is what i just recorded when changing the cell text colour to red.

For ease, record changing the text colours to get the colour codes you need to put in the code (i.e. -16776961 = red on my palette)

Sorry, last question.

How do I get the codes needed for the shades of colour I need?

(In particular - Red, Yellow, Gold, Sea Green and Light Blue)

thanks for helping :D

 

[Peter_SSs]

How do I get the codes needed for the shades of colour I need?

(In particular - Red, Yellow, Gold, Sea Green and Light Blue)

Use the macro recorder...

For ease, record changing the text colours to get the colour codes you need to put in the code (i.e. -16776961 = red on my palette)

 

[wesimmo]

In general, if you're not sure how to do something, use the record macro function.

Although i can do quite a bit in VBA, i have real problems always remembering the exact syntax, its just the way my brain (doesn't) work.

So i often find myself recording the basic functionality and then editing the resulting VBA into my main code.

 

[kat44]

I wasn't used to macros but I have now had a go and it all works perfectly!!

Thank you everyone for your help