+ Reply to Thread
Results 1 to 6 of 6

Complex Conditional Formatting Problem

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question Complex Conditional Formatting Problem

    Hello,

    I have tried to solve this problem by reading these forums, as well as Microsoft's online resources, but I have not found a solution that works. I need each row to fill with red whenever the current date is greater than the expired date. I am currently using a macro to add the storage date each time something is entered into a cell in the Contents List (column D).

    At best, I can get the storage and expired date to be filled with red, or some combination of cells get highlighted, but not the entire row.

    Please help explain what needs to be done and/or provide a solution I can learn from.

    Your help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Complex Conditional Formatting Problem

    Have a look at the new conditional formatting formula in the attached.

    If you need help explaining it, let us know.

    S.
    Attached Files Attached Files
    If I've been of help, please hit the star

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex Conditional Formatting Problem

    change the formula in the CF to...
    =AND($F2<>"",$F2<TODAY())

    =now() includes time as well as date

    edit: and for future reference, dont use "" around your formula....
    ="$F2 < now()"
    Last edited by FDibbins; 01-29-2013 at 07:02 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-29-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question Re: Complex Conditional Formatting Problem

    Quote Originally Posted by Spencer101 View Post
    Have a look at the new conditional formatting formula in the attached.

    If you need help explaining it, let us know.

    S.
    Thank you for your help. I also need to insert a date in the Storage Date column whenever something is added to the Contents List column. Then, the Expiration Date column needs to have the current date plus 60 days. I'm running the following macro in it, but it is not working. Strange part is, I have used this exact same code in other workbooks, and it has worked. Am I not saving it properly? Is there some setting that is not correct? I have attached the workbook sample you gave me, with the new macro. I have also saved it as a macro enabled workbook.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D2:D61")) Is Nothing Then

    Target.Offset(0, 1) = Now()
    Target.Offset(0, 2) = Now() + 60

    End If



    End Sub


    I appreciate the help from all the people who reply to my thread.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex Conditional Formatting Problem

    I am not able to help with VBA hopefully some-one else will offer

  6. #6
    Registered User
    Join Date
    01-29-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Complex Conditional Formatting Problem

    Should this thread be moved to the VBA forums to increase visibility for people who are good with VBA?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1