Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Conditional formatting (Excel 2003)

    Good morning....I am looking for some help with cond. formatting (CF)....Using the 'usual' method of cond formatting will not suffice, and so I am using VBA as a worksheet event (?)...borrowing code from an earlier posting that I did and that Hans helped me with....I am attaching a small sample wbook and I have started the code on the worksheet but I cannot get it to have a bold font where needed. I would also like to have the code apply to multiple events and I have tried to explain that on the worksheet....the code I have developed so far isn't flexible enough to cover the variables and I need help with that (otherwise, I have to write a line for every conceivable variable).....on the other hand, if someone can do devise something using the conventional method of 'conditional formatting', that would work for me as well....thanks, all.
    Attached Files Attached Files

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

    Re: Conditional formatting (Excel 2003)

    Your descriptions are contradictory.
    Cell E7 neither begins nor ends in a number yet you want it to be black on green.
    Cell F8 should be white on red according to the description but you've made it red on transparent.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    I can see that about the descriptions....the red on white should really be red on transparent (I didn't know that there was "transparent")....

    what I am trying to do is have 3 color-codes....(1) CJ-AM and CJ-PM should be bold red on transparent; (2) a work assignment like 9:00 start, or 1:15 start (anything b/w 9:00am and 1:45pm) should be black on gray; work assignments that are 'split' (eg: 405-AM, 309-PM, or CJ-AM, 1105-PM, or 508-AM, DJ-PM...the numbers are room numbers, not times) should be black on green.....I had originally done it using conditional formatting but it didn't always work and so I thought that this method might be better.....does any of this help clarify the problem?

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

    Re: Conditional formatting (Excel 2003)

    Not much. In your workbook you have "CJ - AM", now you mention "CJ-AM". We need PRECISE and EXACT descriptions.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Of course...sorry about that....my descriptions would be CJ - AM, CJ - PM...start times would be in the format of "9:00 start" or "1:15 start" or "1:30 start" or "9:15 start".......the black on green would be anything that is a split assignment.....does that help? (and if the black on green is too wide-open, just ignore it and I'll try to develop something using the more conventional conditional formatting....thank you, Hans

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

    Re: Conditional formatting (Excel 2003)

    The attached version is an attempt to use standard conditional formatting to match your descriptions. I have removed the code.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Thank you, Hans.,....I looked at it quickly, but can the formula you used for condition 3 preclude also be used to go black on green in, for example, cell E7 (where it is 'text-number')....?

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

    Re: Conditional formatting (Excel 2003)

    Could you please describe EXACTLY when a cell should have a green background? <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    It should have a green background when (a) it is "any_number - AM, text" (eg: '1108 - AM, DJ - PM', or '508 - AM, CJ - PM') or "text, any_number - PM" (eg: 'CJ - AM, 407 - PM', or 'DJ - AM, 1102 - PM') or "any_number - AM, any_number - PM" (eg: '405 - AM, 1203 - PM') .....does that explain it better (I hope..) ?....as I look at it, the commonality of all of these seems to be the presence of a whole number, or will go wrong if I see it that way?

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

    Re: Conditional formatting (Excel 2003)

    So the value in cell C8 in the sample worksheet ("DJ - AM TTN Youth") should *not* have a green background because it doesn't contain a number?

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    ...exactly...

  12. #12
    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 (Excel 2003)

    Change the last condition in Hans' sheet to:
    =OR(ISNUMBER(FIND("- AM",C5)), ISNUMBER(FIND("- PM",C5)),NOT(ISERROR(VALUE(LEFT(C5,1)))),NOT(ISERR OR(VALUE(RIGHT(C5,1)))))

    Steve

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

    Re: Conditional formatting (Excel 2003)

    Try this formula in the third condition in Format | Conditional Formatting (with C5 as the active cell):

    =MIN(1*ISERROR(1*MID(C5,ROW($1:$50),1)))=0

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    What is the significance of $1:$50 ??

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

    Re: Conditional formatting (Excel 2003)

    ROW($1:$50) is an array consisting of the numbers 1, 2, ..., 50.
    MID(C5,ROW($1:$50),1) is an array consisting of the 1st, 2nd, ..., 50th character of C5 (I have assumed that the values will not exceed 50 characters in length).
    1*MID(C5,ROW($1:$50),1) is an array consisting of a number where the character is numeric, and an error value where it isn't.
    ISERROR(1*MID(C5,ROW($1:$50),1)) is an array of TRUE/FALSE values - FALSE if the character is numeric, TRUE otherwise.
    1*ISERROR(1*MID(C5,ROW($1:$50),1)) converts TRUE to 1 and FALSE to 0.
    If MIN(1*ISERROR(1*MID(C5,ROW($1:$50),1))) is 0, there was at least one 0, i.e. at least one FALSE value, i.e. the corresponding character is numeric.

    See Array Formulas by Bob Umlas about some of the wonderful applications of array formulas.

Page 1 of 2 12 LastLast

Posting Permissions

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