Quicker way to examine ranges

[Cardstang]

I have some code that looks through a range and determines if it falls within a certain time based on the state. If not, then it colors a row of cells. My states are in column C and my times are in columns N and O (as you can probably tell from the code below).

It's duplicated many times (almost every state) and the only variations are the state names and the times that determines the coloring.


Below is a snippet for Georgia.

Code: Select all

Dim N As Long
For N = 2 To 1000
    If (Range("C" & N) = "Georgia" And Hour(Range("N" & N)) > 7 And Hour(Range("N" & N)) < 22) Or (Range("C" & N) = "Georgia" And Hour(Range("O" & N)) > 7 And Hour(Range("O" & N)) < 22) Then
        Range("A" & N & ":Z" & N).Interior.ColorIndex = 34
    End If
Next N

What I have works just fine, but I'm wondering if there is a quicker or more efficient way of doing this?

Thanks in advance.

[HansV]

Welcome to Eileen's Lounge!

I'd create an auxiliary worksheet (which can be hidden if you prefer) with a lookup table: state names in column A, corresponding times in columns B and C. Let's say that you name this sheet Lookup. You can then use code like this:

Code: Select all

Sub ColorRows()
  Dim n As Long
  Dim m As Long
  Dim wsh As Worksheet
  Dim rng As Range
  Dim t1 As Double
  Dim t2 As Double
  Set wsh = Worksheets("Lookup")
  m = Range("C" & Rows.Count).End(xlUp).Row
  For n = 2 To m
    Set rng = wsh.Range("A:A").Find(What:=Range("C" & n), _
      LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
      t1 = rng.Offset(0, 1)
      t2 = rng.Offset(0, 2)
      If (Range("N" & n) > t1 And Range("N" & n) < t2) Or _
         (Range("O" & n) > t1 And Range("O" & n) < t2) Then
        Range("A" & n & ":Z" & n).Interior.ColorIndex = 34
      End If
    End If
  Next n
End Sub

See the attached sample workbook.

ColorSample.xls

[Cardstang]

Hi HansV.

That worked magnificently. Thank you.

Had to modify a bit because the data in columns N and O is HH:MM:SS.

If (Hour(Range("N" & n)) > t1 And Hour(Range("N" & n)) < t2) Or _
(Hour(Range("O" & n)) > t1 And Hour(Range("O" & n)) < t2) Then

I'll play this and tweak as needed. It's a great starting point for me.

One question I do have, and perhaps explaining a bit more on what I'm examining will help you understand.

I'm evaluating times that are converted to US Central Time (which is where I sit). The central time ranges I need to highlight are 8:00 and 23:00. US East is an hour earlier (7:00 and 22:00). The times in the columns are a "begin" and "end" time. In the example you provided, Time2 is the Begin and Time1 is the End. What I'm actually looking for is anything that is outside of a time from of 23:00 - 8:00AM the next day for the local window. Essentially a 9 hour time window.

How about time zones that are "behind" me and don't fit easily into a < or > formula? Mountain would be Midnight start and a 9:00 End. Pacific would be a 1:00 Start and a 10:00 End. I've plugged those times in and I'm not getting the result I'm expecting.

I'll continue messing with it, but is there something I'm missing? Or am I making this too complicated?

Thanks again.

[HansV]

You could enter 1:00 AM as 25:00. Excel will still display it as 1:00 but because of the extra 24 hours, Excel "knows" that it's the next day.

[Cardstang]

Perfect!

Thank you so much.