Highlighting Range Based On Time

[mctopher]

I have a column that has a list of different times, and I need to figure out how to write code to highlight every other hour block. I want to set a highlight for the group of cells in the 6:00 hour, the 8:00 hour, 10:00 and so on.

I've tried to set a counter, however given the way excel views times as though they were a date, I'm having problems.

Here is a sample of the data which is in Column A formatted Custom as "hh:mm:ss" and starting on row 4:

6:00 AM
6:40 AM
6:52 AM
7:04 AM
7:10 AM
7:11 AM
7:43 AM
7:51 AM
7:59 AM
8:07 AM
8:19 AM
8:52 AM
9:05 AM
9:06 AM
9:14 AM

Here's what I have so far as a work in progress.

HourStartRow = 4
HighlightSwitch = 1
For i = 4 To ScheduleLastRow

    If Schedule.Range("A" & i).Value > Schedule.Range("A" & HourStartRow).Value + 1 Then 'should be +1 hour
            HourEndRow = i - 1
    Else
    End If
    
    If Not HourStartRow = Empty And Not HourEndRow = Empty Then
    
        If HighlightSwitch > 0 Then
            HighlightSwitch = HighlightSwitch * (-1)
            Schedule.Range(Cells(1, HourStartRow), Cells(1, HourEndRow)).Interior.ColorIndex = 6
            HourStartRow = HourEndRow + 1
            HourEndRow = Empty
        Else
            HighlightSwitch = HighlightSwitch * (-1)
            HourStartRow = HourEndRow + 1
            HourEndRow = Empty
    Else
    End If

Next

As you can see, I think I have everything except for figuring out how to add an hour. (And I've got to admit, I was proud of myself for coming up with the HighlightSwitch to only hit every other hour). But that adding an hour is killing me.

Thoughts? Any help would be very appreciated. Thanks!

 

[AD_Taylor]

Something like this?

Sub highlightHours()    
    Dim sRow As Integer
    Dim lRow As Integer
    Dim rng As Range
    
    Dim hour1 As Integer
    Dim hour2 As Integer
    
    Dim i As Integer
    
    sRow = 1
    lRow = Schedule.Range("A" & Rows.Count).End(xlUp).Row
    hour1 = hour(Schedule.Range("A" & sRow).Value)
    
    For i = sRow To lRow
        Set rng = Schedule.Range("A" & i)
        hour2 = hour(rng.Value)
        
        If (hour2 - hour1) Mod 2 = 0 Then
            rng.Interior.ColorIndex = 6
        End If
    Next i
End Sub

 

[HalfAce]

AD_Taylor:

Is it wrong that I understand VBA/Macros more than I understand formulas?

Nope, not in my book anyway.
I feel like I suck at formulas and always find myself writing a 30 (or more) line routine only to see Aladin or someone do the same thing with a 20 character formula!

VBA can save the world!

 

[mctopher]

Something like this?

EXACTLY like that!! Thank you so much! I wasn't aware of the Hour function, so that I'm sure will be a huge help going forward.

Mod function is new to me. I've done a little looking and now know that it returned the remainder, but I'm not sure how the logic is working here to help skip to every other hour. Could you help me understand how that line of code is accomplishing the highlights for every other hourly block?

Thanks again!

 

[AD_Taylor]

@HalfAce - I know the feeling! My best example is trying to understand array formulas, blows my mind. So I just write a quick looping macro, much easier to understand!

@mctopher - Great glad it worked for you!

The Mod function works here as you say to get the remainder but in order to do this it employs modulo arithmetic. This basically means that 0 and 2 are equal. So if I'd put x Mod 3 instead then 3 and 0 are classed as equal.
So in the example above we get output similar to the following:

Assuming the first hour is 6,

6 - 6 = 0, Mod does nothing to this
7 - 6 = 1, Mod does nothing to this
8 - 6 = 2, but Mod makes 2 = 0 so this actually = 0
9 - 6 = 3, but since 3 = 1 + 2 and 2 is equal to 0 so this actually = 1

I think my explanation of the (9 - 6) one is about jumbled but you should get the idea. Everytime x Mod 2 finds a multiple of 2 (or even number), it returns 0 and for anything else it returns 1.
In the code example posted, this has the effect of subsequent hours being highlighted - the hour in the first row will always be highlighted.

HTH!