Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

In the code below, when the hour changes a certain range of cells are highlighted…

  1. User

    In the code below, when the hour changes a certain range of cells are highlighted Yellow. The problem I am having is as described below:

    When the hour changes the cells in row 5 & 6 are highlighted in the correct position. But the cells in both row 11 & 12 and row 17 & 18 are highligted in the wrong position.

    In the example below, when K5:L6 are highlighted, K11:L12 and K17:L81 should also be highlighted. Currently, when K5:L6 is highlighted, M11:N12 and M17:N18 are highlighted which should not be the case.

    Sub CopyAA() ' Copy AA Schedule for the current Day Dim today As String today = Format(Now, "ddd") Dim sourceRange As Range Dim destRange As Range Set sourceRange = Worksheets("AA").Range("A3:L3") ' Source range in the "AA" sheet Set destRange = Worksheets("Daily").Range("A9:L9") destRange.ClearContents sourceRange.Copy destRange.PasteSpecial Paste:=xlPasteFormats Select Case today Case "Mon" sourceRange.Copy destRange Case "Tue" sourceRange.Offset(1).Copy destRange Case "Wed" sourceRange.Offset(2).Copy destRange Case "Thu" sourceRange.Offset(3).Copy destRange Case "Fri" sourceRange.Offset(4).Copy destRange End Select Application.CutCopyMode = False Application.Wait Now + TimeValue("00:00:01") Call CopyCL End Sub

    Sub CopyCL() ' Copy CL Schedule for the current Day Dim today As String today = Format(Now, "ddd") Dim sourceRange As Range Dim destRange As Range Set sourceRange = Worksheets("CL").Range("A3:O3") ' Source range in the "CL" sheet Set destRange = Worksheets("Daily").Range("F15:T15") destRange.ClearContents sourceRange.Copy destRange.PasteSpecial Paste:=xlPasteFormats Select Case today Case "Mon" sourceRange.Copy destRange Case "Tue" sourceRange.Offset(1).Copy destRange Case "Wed" sourceRange.Offset(2).Copy destRange Case "Thu" sourceRange.Offset(3).Copy destRange Case "Fri" sourceRange.Offset(4).Copy destRange End Select Application.CutCopyMode = False Application.Wait Now + TimeValue("00:00:01") Call ClearCellBackgroundColors End Sub

    Sub ClearCellBackgroundColors() ' Clear Yellow Time Background colour Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Daily") With ws.Range("A5:U6") .Interior.Color = xlNone End With With ws.Range("A11:U12") .Interior.Color = xlNone End With With ws.Range("A17:U18") .Interior.Color = xlNone End With Application.Wait Now + TimeValue("00:00:01") Call ShowTime End Sub

    Sub ShowTime() ' Highlight Time Periods in Yellow Dim currentTime As Date currentTime = Now Dim ranges(27) As Range Dim timePeriods(10) As Date Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Daily") Set ranges(1) = ws.Range("C5:D6") Set ranges(2) = ws.Range("C11:D12") Set ranges(3) = ws.Range("C17:D18") Set ranges(4) = ws.Range("E5:F6") Set ranges(5) = ws.Range("E11:F12") Set ranges(6) = ws.Range("E17:F18") Set ranges(7) = ws.Range("G5:H6") Set ranges(8) = ws.Range("G11:H12") Set ranges(9) = ws.Range("G17:H18") Set ranges(10) = ws.Range("I5:J6") Set ranges(11) = ws.Range("I11:J12") Set ranges(12) = ws.Range("I17:J18") Set ranges(13) = ws.Range("K5:L6") Set ranges(14) = ws.Range("K11:L12") Set ranges(15) = ws.Range("K17:L18") Set ranges(16) = ws.Range("M5:N6") Set ranges(17) = ws.Range("M11:N12") Set ranges(18) = ws.Range("M17:N18") Set ranges(19) = ws.Range("O5:P6") Set ranges(20) = ws.Range("O11:P12") Set ranges(21) = ws.Range("O17:P18") Set ranges(22) = ws.Range("Q5:R6") Set ranges(23) = ws.Range("Q11:R12") Set ranges(24) = ws.Range("Q17:R18") Set ranges(25) = ws.Range("S5:T6") Set ranges(26) = ws.Range("S11:T12") Set ranges(27) = ws.Range("S17:T18") timePeriods(1) = "06:59:59" ' 6:59am - before any time period" timePeriods(2) = "08:00:00" timePeriods(3) = "09:00:00" timePeriods(4) = "10:00:00" timePeriods(5) = "11:00:00" timePeriods(6) = "12:00:00" timePeriods(7) = "13:00:00" timePeriods(8) = "14:00:00" timePeriods(9) = "15:00:00" timePeriods(10) = "16:00:00" ' 4pm - after all time periods" Dim i As Integer For i = 1 To 27 Dim rangeIndex As Integer If i Mod 3 = 1 Then ' Determine the time period based on the index of the range rangeIndex = (i + 2) \ 3 Else rangeIndex = ((i + 2) \ 3) - 1 End If If TimeValue(currentTime) >= TimeValue(timePeriods(rangeIndex)) And TimeValue(currentTime) < TimeValue(timePeriods(rangeIndex + 1)) Then ranges(i).Interior.Color = RGB(255, 255, 0) ' Change the background color to Yellow End If Next i Application.Wait Now + TimeValue("00:00:01") End Sub

  2. Assistant

    The issue you are facing seems to be with the indexing of the ranges in the ShowTime subroutine. The current logic for determining the rangeIndex works fine for the first 3 ranges, but for the subsequent ranges, it is off by one. To fix this, you need to modify the calculation of the rangeIndex for ranges 4 and onwards.

    Replace the following code in the ShowTime subroutine:

    If i Mod 3 = 1 Then ' Determine the time period based on the index of the range
        rangeIndex = (i + 2) \ 3
    Else
        rangeIndex = ((i + 2) \ 3) - 1
    End If
    

    With the following updated code:

    If i Mod 3 = 1 Then ' Determine the time period based on the index of the range
        rangeIndex = (i + 2) \ 3
    Else
        rangeIndex = ((i + 2) \ 3)
    End If
    

    This will fix the issue you mentioned and ensure that the correct ranges are highlighted when the hour changes.