Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formating (2003 SP2)

    Good morning

    Is there a way to conditional format a cell to accept different letters, for example I wanted a cell to turn yellow if an M, L, T or UA was entered into the cell, I tried the equals to option by putting ="M","L","T","UA" but it did not like it, if it can be done how do I phrase it

    Thank you

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    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 Formating (2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 01-Nov-07 07:38. Added PS, PPS)</P>Select A1
    Insert -name- define
    Name:
    MatchMe [Change name as desired]
    Refers to:
    =ISNUMBER(MATCH(A1,{"M","L","T","UA"},0))
    [OK]

    Select the range of cells for cond format
    Format - conditional formatting...
    Formula is
    =MatchMe
    [Format...]
    patterns(tab)
    Select yellow color
    [ok][ok]
    If the cell contains any of the items from the list given, it will be yellow.

    An alternate solution to not use the name is to include a range of cells with the items to match (presume that "M","L","T", and "UA" are in D14). If the cond format is in cell A1:

    Format - conditional formatting...
    Formula is
    =ISNUMBER(MATCH(A1,$D$1:$D$4,0))
    [Format...]
    patterns(tab)
    Select yellow color
    [ok][ok]


    Steve
    PS you may not use the formula:
    =ISNUMBER(MATCH(A1,{"M","L","T","UA"},0))
    Directly as a cond formatting formula due to limitations in the coding. Arrays are not allowed...

    PPS I suppose you could even use the more direct solution:
    Format - conditional formatting...
    Formula is
    =or(A1="M",A1="L",A1="T",A1="UA")
    patterns(tab)
    Select yellow color
    [ok][ok]

    I prefer the MATCH with the list ....

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formating (2003 SP2)

    Hi Steve

    Thanks for your response, I fear that I may not have expressed the question correctly causing you a lot of bother. This all relates to my Holiday Planner where I am trying to avoid at all costs if possible to use VBA or Macros because I do not understand them and would not want to just copy and paste other peoples work without knowing why it works.

    As pointed out to me by HansV Conditional Formatting is ideal for 3 colours so to try and cheat I thought that if an H was entered for Holiday the cell would turn red, Sick = Green, Maternity = Pink, I then got to thinking that the Red and Green would be the main ones so if I had a Miscellaneous ie. Yellow I could use COUNTIF's to show the number of M-Maternity, L-Lieu, T-Training and UA-Unauthorised

    Probably becuase of my normal ineptitude I could not get any of the options to work, this may be in part due to the number of cells that require this condition D8:AH67, I had a limited success with the 'PPS' in your mail and selected all of the cells in the range and altertered the expression to

    =OR($D$8:$AH$67="M",$D$8:$AH$67="L",$D$8:$AH$67="T ",$D$8:$AH$67="UA")

    with the unexpected result that it turned the whole chart Yellow but it is on the right track, it seems daunting though as there are 2077 cells that require the format (31 columns x 67 rows at present) to have to enter the original formula into each and every cell x 12 for the months will take me a day or two!

    Thanks very much indeed for you input and I will continue to experiment along these lines

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    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 Formating (2003 SP2)

    You have to put individual cells in (eg just A1).

    If you select D8:AH67 you only have to enter in the formula:
    =or(D8="M",D8="L",D8="T",D8="UA")

    and it will "copy" the formula correctly since you are making the D8 relative...

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formating (2003 SP2)

    Ah, thats better, Thank you Steve, I was trying to be to smart (if thats possible) works like a charm now

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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