Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Conditional Formatting of Text (Excel 2003 )

    I am needing to have the text of a cell be a different color based on a 'text' condition. I thought the example from this post would work, but it doesn't. What I need is if the value in cells A2:L50 = Durham, then the text in cell P2 would be red. Any help would be appreciated.

    http://www.wopr.com/cgi-bin/w3t/showflat.p...ew=&sb=&o=&vc=1

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    You want the text in cell P2 to be Red if all of the cells in the range A2:L50 contain the text "Durham"?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    If any of the cells are text "Durham", then I want it to be red. I don't need to count how many instances of "Durham" there are, I just need to know that if "Durham" is in any of the cells I want it to be red.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Hi,

    Easy:
    . Select A2:L50
    . Use Format|Conditional Formatting
    . Set the first box to "Cell value is equal to"
    . Set the second box to "Durham"
    . Choose Format|Patterns and click on a red box.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Ok, that turns the font to red in those cells, but I want "Durham" in cell P2 to turn red if it occurs in those cells.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Select P2
    Format - conditional formatting...
    Formula is
    =COUNTIF(A2:L50,"Durham")
    [Format]
    Font (tab)
    Color (pick list)
    Select RED
    [OK][[OK]

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Thank you Steve. I thought COUNTIF could only be used if you were actually wanting to count how many occurrences a 'text' value occurred in a given range. That's what I needed.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    That is what COUNTIF does, but since Excel treats 0 as False and any other number as True, you can also use it to the way Steve has here.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Conditional Formatting of Text (Excel 2003 )

    If there are no cells containing "Durham", COUNTIF returns 0, otherwise it returns a positive number. Steve uses the fact that 0 is equivalent to FALSE and that any other number is considered to be not FALSE, i.e. TRUE.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Thanks, everyone. Another little trick under my hat.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    If it would help you to understand the logic (for the future), you could use as the formula:
    =COUNTIF(A2:L50,"Durham")>0

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting of Text (Excel 2003 )

    Thanks again.

    I grew up on a farm in Brazil (not using computers at all) so most everything I learn about computers is by trial and error. I appreciate your patience with my questions.

Posting Permissions

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