Results 1 to 6 of 6

Thread: highlight cell according to date difference
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default highlight cell according to date difference

    Hello everyone!

    I' like a cell to be automatically hightlighted when the date in that cell is 15 days older than the day I type in that cell!

    For example, today is October 21st, and in cell F5 I am typing the date October 1st. I'd like that cell to be highlited (I'm going to use it for the cells F5 to F10000), because the difference is more than 15 days.
    (I work in a lab and it is the day of the sample receipt (column F) and the day we process the sample)

    I thought of doing it using conditional formating and today function, but this means that gradually all my dates will be more than 15 days old. I saw there is an option to stop automatic calculations, but I can't use that, because the whole sheet is full of functions that I need to be updated automatically.

    I also thought of adding an extra column with the day I type in the data, which would probably work just fine, but I'm trying to minimise the thingsI need to insert in the sheet.

    Thank you in advance

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: highlight cell according to date difference

    So, do you only want it to check that as you enter the value in the cell (and not done the road, for values already entered in)?
    If so, you can use VBA to do that.

    Righ-click on the sheet tab name (at the bottom of the screen) that you wish to apply this too, select View Code, and paste this code in the resulting VB Editor window:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
        
        Set rng = Intersect(Target, Range("F5:F10000"))
        
        If rng Is Nothing Then Exit Sub
        
        For Each cell In rng
            If (cell <> "") Then
                If (Date - cell > 15) Then cell.Interior.Color = 65535
            Else
                cell.Interior.Pattern = xlNone
            End If
        Next cell
    
    End Sub
    This should do what you want automatically as you enter data into F5:F10000
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: highlight cell according to date difference

    Quote Originally Posted by Joe4 View Post
    So, do you only want it to check that as you enter the value in the cell (and not done the road, for values already entered in)?
    If so, you can use VBA to do that.
    This should do what you want automatically as you enter data into F5:F10000
    Yes, that's exactly what I need, thank you! Is it possible to reset to no highlight when I correct that cell value?

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: highlight cell according to date difference

    Try this slight variation:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
        
        Set rng = Intersect(Target, Range("F5:F10000"))
        
        If rng Is Nothing Then Exit Sub
        
        For Each cell In rng
            If (cell <> "") And (Date - cell > 15) Then 
                cell.Interior.Color = 65535
            Else
                cell.Interior.Pattern = xlNone
            End If
        Next cell
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: highlight cell according to date difference

    Quote Originally Posted by Joe4 View Post
    Try this slight variation
    Just tried it and it works perfectly, thank you!

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: highlight cell according to date difference

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

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
  •