Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Conditional formatting question

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I am using the following formula for conditional formatting:

    =COUNTIF($A$70:$A$84,$E5)

    It checks to see if E5 (actually every cell in E) matches A70-84 and if so it changes the format. A70-84 contains a list of state abbreviations & it works great. However, now I want to change the information that the changes the formatting.

    Instead of state abbreviations, I want the formatting to change IF there is a DATE being held in the cells in E. It doesn't matter what the date is, just that it is a date. Any other data should be ignored.

    Any pointers on this one?

    Thanks!

    Paul

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, here is my shot at this, although I bet someone may suggest a more efficient formula. This worked for me in most cases (see below for details):

    =IF(LEN(A70)<5,"",OR(WEEKDAY(A70)=1,WEEKDAY(A70)=2,WEEKDAY(A70)=3,WEEKDAY(A70)=4,WEEKDAY(A70)=5,WEEKDAY(A70)=6,WEEKDAY(A70)=7))

    Then copy as needed.

    The LEN condition refers to the minimal 5 digit date serial number.

    This formula is not perfect, in that if a 6 or 7 digit number is entered, Excel may regard that as a date also and conditionally format the cell, even if your intention for that value is not to have it represent a date, even if you precede it with an apostrophe. It will not format the cell for a value less than 5 characters in length (even if all are digits), nor will it format if an 8-digit value is entered.

    Except for those restrictions, does this help? I bet someone will show me where this formula could be improved.

    It's do-able with VBA also.

    I'd be interested in anyone's suggestion for a tighter conditional format formula solution, but hopefully this may get you started.

    Any help?

    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-05-01 07:09 ]

  3. #3
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's fairly simple as long as you dates aren't in a custom format.

    =AND(LEFT(CELL("format",E1),1)="D",RIGHT(CELL("format",E1),1)*1<6) checks E1 for a standard date format.

    [ This Message was edited by: Steve Hartman on 2002-05-01 08:10 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(LEN(A70)<5,"",OR(WEEKDAY(A70)=1,WEEKDAY(A70)=2,WEEKDAY(A70)=3,WEEKDAY(A70)=4,WEEKDAY(A70)=5,WEEKDAY(A70)=6,WEEKDAY(A70)=7))

    This works, but only to change the date to a different format, not to convert the entire row to a different format. What happens is Excel automagically changes the row letter so the formula applies to individual cells in a row rather than the entire row - A70, B70, C70, etc. (I'm a newbie, so I'm assuming this is a simple fix)

    Paul

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I did not gather from your original question that you wanted other cells in the same row formatted, depending on the existence of a date in column A of that given row.

    No problem though, just use this modified formula and copy the conditional formatting to the cells of your choice:

    =IF(LEN($A70)<5,"",OR(WEEKDAY($A70)=1,WEEKDAY($A70)=2,WEEKDAY($A70)=3,WEEKDAY($A70)=4,WEEKDAY($A70)=5,WEEKDAY($A70)=6,WEEKDAY($A70)=7))

    Is that what you want?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tom!

    Yes that is exactly what I wanted. So, (warning warning newbie comment) the $ in front of the letter is what keeps it from automatically moving up a letter? My company has Office on every machine but no manual, no training, nothing - I know it a bit, but when it comes to doing things like formatting, etc., I'm lost.

    Thanks again!

    Paul

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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