Results 1 to 11 of 11
  1. #1
    BakerMan
    Guest

    Lists and formatting (2000)

    Hello, I have a spreadsheet attached that is my Vacation schedule. I managed to use Conditional Formatting to highlight dates that are duplicated when entered.

    Is there a way to automatically make a list the duplicate dates?

    I would also like to highlight any cell if todays date has been entered, I tried Conditional Formatting - formula is: =today() -also -now() but all the cells highlight??????????
    thanks

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

    Re: Lists and formatting (2000)

    If you want to highlight today, use Formula Is with the following formula:

    =(B3=TODAY())

    where B3 is the active cell in the selection; it will automatically be adjusted for the other cells in the selection. This won't work for the cells containing two separate days, though.

    How would you like duplicate dates to be listed?

  3. #3
    BakerMan
    Guest

    Re: Lists and formatting (2000)

    I would like the date listed with the Person's name beside it. Where doesn't really matter. thanks

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

    Re: Lists and formatting (2000)

    Your conditional formatting seems to check for duplicates between employees as well as within the dates for one employee, for example 17-Dec is highlighted in J6 and K3. Do you want to include this in your list, or are you just looking for duplicates within the dates for each individual employee?

  5. #5
    BakerMan
    Guest

    Re: Lists and formatting (2000)

    I would like to generate a report that I can print out of people on vacation at the same time.
    PS - The highlight for today worked great. I truly appreciate all the help I get from this board. Thanks so much.

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

    Re: Lists and formatting (2000)

    Sorry to keep on asking questions. What is the exact range you want to test for duplicates?

    By the way, having two dates in some cells is going to make it much more difficult.

  7. #7
    BakerMan
    Guest

    Re: Lists and formatting (2000)

    I suppose as long as it detects the name of the person and the date that's duplicated, I cold use the entire range B3:T20 or individually for each named range
    Vacdates=B3:T7
    Compliance=B8:T9
    Training=B10:T14
    Houston=B15:T19
    Borger=B20:T20
    If the listed departments (named ranges) are reported separately - then that's ok too. Whichever is easiest. thanks for your time.

  8. #8
    BakerMan
    Guest

    Re: Lists and formatting (2000)

    I can eliminate the dual dates in one cell. I will just use a comment. thanks

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists and formatting (2000)

    See if this VBA procedure will give you what you want (to work properly, the two dates in one cell will have to be removed):

    <pre>Public Sub FindDups()
    Dim I As Long, J As Long, lLastRow As Long, K As Long, L As Long, M As Long, N As Long
    Dim bFirstDup As Boolean, bFndDup As Boolean
    Dim oTgtSh As Worksheet, oSrcSh As Worksheet
    Set oSrcSh = Worksheets("Sheet1")
    On Error Resume Next
    Set oTgtSh = Worksheets("Overlap")
    On Error GoTo 0
    If oTgtSh Is Nothing Then
    Set oTgtSh = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgtSh.Name = "Overlap"
    oSrcSh.Activate
    End If
    oTgtSh.Cells.Clear
    lLastRow = oSrcSh.Range("A65536").End(xlUp).Row - 1
    M = 0
    For I = 0 To lLastRow - 1
    bFirstDup = True
    For J = 0 To lLastRow
    If I <> J Then
    bFndDup = False
    N = 0
    For K = 0 To 14
    For L = 0 To 14
    If oSrcSh.Range("F3").Offset(I, K).Value <> "" Then
    If oSrcSh.Range("F3").Offset(I, K).Value = oSrcSh.Range("F3").Offset(J, L).Value Then
    If bFirstDup = True Then
    oTgtSh.Range("A1").Offset(M, 0).Value = oSrcSh.Range("A3").Offset(I, 0).Value
    bFirstDup = False
    M = M + 1
    End If
    If Not bFndDup Then
    oTgtSh.Range("A1").Offset(M, 0).Value = oSrcSh.Range("A3").Offset(J, 0).Value
    bFndDup = True
    End If
    oTgtSh.Range("B1").Offset(M, N).Value = oSrcSh.Range("F3").Offset(I, K).Value
    oTgtSh.Range("B1").Offset(M, N).EntireColumn.AutoFit
    N = N + 1
    End If
    End If
    Next L
    Next K
    If bFndDup Then M = M + 1
    End If
    Next J
    If M > 0 And oTgtSh.Range("A1").Offset(M - 1, 0) <> "" Then M = M + 1
    Next I
    End Sub
    </pre>

    Legare Coleman

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

    Re: Lists and formatting (2000)

    You could use code like this. It uses the "vacdates" range, but you can easily adapt it to use other ranges.

    Sub ListDuplicates()
    Dim rng As Range, cel1 As Range, cel2 As Range
    Dim i As Long, j As Long
    Dim lngRow As Long
    Set rng = Range("vacdates")
    lngRow = 30
    For i = 1 To rng.Cells.Count
    Set cel1 = rng.Cells(i)
    If IsDate(cel1) Then
    For j = i + 1 To rng.Cells.Count
    Set cel2 = rng.Cells(j)
    If cel2 = cel1 Then
    lngRow = lngRow + 1
    Cells(lngRow, 1) = cel1
    Cells(lngRow, 2) = Cells(cel1.Row, 1)
    Cells(lngRow, 3) = Cells(cel2.Row, 1)
    End If
    Next j
    End If
    Next i
    Range("A30").Sort Key1:=Range("A30"), Key2:=Range("B30"), Key3:=Range("C30"), Header:=xlYes
    Set cel2 = Nothing
    Set cel1 = Nothing
    Set rng = Nothing
    End Sub

    See the attached workbook (zipped). You must have macro security set to Medium (recommended) or to Low, for if it is set to High you won't be able to run the macro.

  11. #11
    BakerMan
    Guest

    Re: Lists and formatting (2000)

    Guys - This is the stuff. Thanks again, you're the best! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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