Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Format Font ? (Excel 2003)

    Hi

    I have tried to adapt some of this info, found on thenet to use ie <!t>[red][<-100]General;<!t>[blue][<-20]General;<!t>[yellow]General;@ with no success.
    But instead of numeric I need to format text

    I need to say <!t>[Red]["C"]General;<!t>[blue]["S"]General;<!t>[green]["D"]General;<!t>[yellow]["T"]General;@

    Many Thanks

    Braddy

    Getting 6 conditional font colors without macros
    XL's Conditional Formatting (Format/Conditional Formatting...) allows 3 conditional formats to be applied. Users often ask for ways to extend that to 4 or more formats. If you're changing background color, borders, bolding, etc., you're best off using the Worksheet_Change() or Worksheet_Calculate() event macros to accomplish this.

    However, if you're simply looking to change font color, you can use XL's custom formatting codes to provide up to 3 more options.

    Formatting codes consist of 4 fields separated by semicolons. By default, the first field is the format applied to positive numbers, the second to negative numbers, the third to zero and the fourth to text. You can adjust that with conditional statements, however. Say we wanted to format according to the following scheme:

    Value Font Color
    ===== ==========
    < -100 Red
    < -20 Blue
    < 0 Yellow
    < 20 Green
    < 100 Maroon
    >= 100 Purple

    The last three we'll leave for conditional formatting. The first three, however, will be done using this format, which we enter by choosing Format/Cells/Number/Custom:

    <!t>[red][<-100]General;<!t>[blue][<-20]General;<!t>[yellow]General;@
    Of course, we don't have to use General - we could use any other numeric format. The available color names are <!t>[black], <!t>[blue], <!t>[CYAN], <!t>[green], <!t>[MAGENTA], <!t>[red], <!t>[white], and <!t>[yellow], or you can use any of the 56 colors in the XL color palette by using [Color1] through [Color56]. Thanks, MVP Bob Umlas for telling me about the latter method!
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Font ? (Excel 2003)

    That type of custom formatting is meant for numbers, not for text. You can use the On Change event of the worksheet to change the font colour if the value of a cell changes. See for example <post:=442,489>post 442,489</post:>.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Format Font ? (Excel 2003)

    Hi Hans

    Thanks for the direction, I will give it a try, after England have beaten Australia. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format Font ? (Excel 2003)

    Braddy

    Although this does not answer your question directly as you are using text you can have 6 numeric values formatted to different colours using a mixture of your first method and conditional formatting.

    1) Select your range
    2) Format|Cells..., select Number|Custom
    3) Type in, say, <!t>[red]<!/t>[=83]General;<!t>[blue]<!/t>[=84]General;<!t>[green]<!/t>General;@
    4) Press OK

    With the same range selected

    5) Format|Conditional Formatting
    6) Select Cell Value is equal to.....type in the value, say 67 or 68
    7) Add to more conditions similar to 6)
    8) Press OK

    You will now have 6 different colours for your values.

    PS You may notice that 67, 68,83 and 84 are the =code() values of your letters C,S,D and T as I was experimenting.
    Jerry

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Format Font ? (Excel 2003)

    Hi Jezza

    That looks interesting, I will certainly be able to use this elsewhere.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •