Can VBA can be used to highlight cells in-between times, if a cell figure matches another.

[Paul365]

Hello,

I was wondering if there is a possibility that VBA can be used to highlight cells in-between times, if a cell figure matches another. or a conditional formatting formula

Example (Using Time)
A2 - A 70 is marked (A2)7:00am, (A3)7:15 am, (A4)7:30 am etc.
If I insert a time in a different sheet (lets say sheet 2)- I would like the same cells to highlight in-between times.
Example
Cell A2 on sheet 2 - I type (Start time)8:00 am - and B1 I type (Finish Time)1:00 pm
I would Like Sheet 1 to highlight a color from A6 to A38 automatically


Thanks - any help would be much appreciated

 

[Legacy 143009]

I hope you don't have any typo. This will check Sheet 2 A2 for start time and B1 for finish time.

Sub myFunction()
  Dim lRow As Integer
  lRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To lRow
    If Worksheets("Sheet1").Cells(i, 1).Value >=  Worksheets("Sheet2").Cells(2, 1).Value And Worksheets("Sheet1").Cells(i, 1).Value <=  Worksheets("Sheet2").Cells(1, 2).Value Then
      Worksheets("Sheet1").Cells(i, 1).Interior.ColorIndex = 6
    End If
  Next
End Sub