No Joy with conditional formatting
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: No Joy with conditional formatting

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a column of dates. I also have a ststus column showing if the job is "C" (completed) or "A" (active). I compare the column of dates to the system date located in cell $J$1. If any of the dates in the column are less than $J$1, the date becomes bold and Red on a yellow field, unless, there is a "C" in the status column.
    I wrote a conditional formating formula that said =IF(M6:M100,"C") No change to text.
    I added another that read "Cell was less than $J$1" Change text to Bold Red w/ yellow field.
    When the system date changes to greater than that cell, ALL changes to Bold Red, including the exempted "C" items.

    Any help would be appreciated.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,864
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-04 06:57, kojak43 wrote:
    I have a column of dates. I also have a ststus column showing if the job is "C" (completed) or "A" (active). I compare the column of dates to the system date located in cell $J$1. If any of the dates in the column are less than $J$1, the date becomes bold and Red on a yellow field, unless, there is a "C" in the status column.
    I wrote a conditional formating formula that said =IF(M6:M100,"C") No change to text.
    I added another that read "Cell was less than $J$1" Change text to Bold Red w/ yellow field.
    When the system date changes to greater than that cell, ALL changes to Bold Red, including the exempted "C" items.

    Any help would be appreciated.
    Trying to figure out of what you've done so far is not easy.

    M houses apparently the status values.

    I'll just assume that the dates of interest are in L from L6 on.

    Select L6:L100.
    Activate Format|Conditional Formatting.
    Choose Formula Is for Condition 1.
    Enter in the formula box:

    =AND(L6<$J$1,M6<>"C")

    Activate Format.
    Set up "bold and Red on a yellow field" as formatting on the Font and Patternss tab.
    Click OK, OK.

    Hope this is what you want.


  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need to use two conditional formats in the correct order.
    First set a condition that checks the status column for a value of C and apply no formatting. Then set the second condition to check the date against yout system date and format to turn the date Red when desired.

  4. #4
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry it was not easy to understand my problem, however, your solution nailed it!

    I will have to research what AND means as part of your formula, but it works fine.

    Thanks to you both, (I added another condtion where there is no data, the column is blank) I got it where I want it!

    Thanks
    K

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
  •  

 

 
DMCA.com