Copying Font from cell issues

kat44

New Member
Joined
Aug 3, 2010
Messages
5
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 3, 2010
Messages
5
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

Board Regular
Joined
Nov 23, 2005
Messages
149
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

New Member
Joined
Aug 3, 2010
Messages
5

ADVERTISEMENT

Thank you.

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

Thanks again :biggrin:
 

kat44

New Member
Joined
Aug 3, 2010
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 23, 2005
Messages
149
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

New Member
Joined
Aug 3, 2010
Messages
5
I wasn't used to macros but I have now had a go and it all works perfectly!!

Thank you everyone for your help :biggrin:
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,228
Messages
5,509,922
Members
408,764
Latest member
Abdul Matin

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top