VB 自动设置满足条件的单元格的颜色

最近帮一个朋友求助，问能不能把excel里某一列的时间钟，把大于一个时间的单元格标记出来。有一个参考时间，从参考时间算起往后数3天，遇到节假日自动顺延。

代码如下

Private Sub Workbook_Open()
Dim rng1 As Range
Dim rng2 As Range

Dim i As Integer
Dim flag As Boolean
Dim j As Integer
Dim allrows As Integer

Dim tempDate As Date
Dim validateDates(1 To 3) As Date
Dim japanHolidays(1 To 17) As Date

Set rng1 = ThisWorkbook.Application.Range("A1")
allrows = ThisWorkbook.Application.Range("B65536").End(xlUp).Row
Set rng2 = ThisWorkbook.Application.Range("B1:B" & allrows)

i = 1
flag = True
j = 1

tempDate = DateSerial(Year(rng1.Value), Month(rng1.Value), Day(rng1.Value))


japanHolidays(1) = #1/1/2015#
japanHolidays(2) = #1/12/2015#
japanHolidays(3) = #2/11/2015#
japanHolidays(4) = #3/21/2015#
japanHolidays(5) = #4/29/2015#
japanHolidays(6) = #5/3/2015#
japanHolidays(7) = #5/4/2015#
japanHolidays(8) = #5/5/2015#
japanHolidays(9) = #5/6/2015#
japanHolidays(10) = #7/20/2015#
japanHolidays(11) = #9/21/2015#
japanHolidays(12) = #9/22/2015#
japanHolidays(13) = #9/23/2015#
japanHolidays(14) = #10/12/2015#
japanHolidays(15) = #11/3/2015#
japanHolidays(16) = #11/23/2015#
japanHolidays(17) = #12/23/2015#

While flag
    If Weekday(tempDate, vbMonday) > 5 Or isHoliday(tempDate, japanHolidays) Then
       tempDate = DateAdd("d", 1, tempDate)
    Else
        validateDates(i) = tempDate
        i = i + 1
        tempDate = DateAdd("d", 1, tempDate)
            If i = 4 Then
                flag = False
            End If
    End If
Wend

Debug.Print tempDate


For j = 1 To rng2.Rows.Count
    rng2.Cells(j, 1).Select
    
    If shouldMarked(DateSerial(Year(rng2.Cells(j, 1).Value), Month(rng2.Cells(j, 1).Value), Day(rng2.Cells(j, 1).Value)), validateDates) Then
        
        rng2.Cells(j, 1).Interior.ColorIndex = 3
        Else
            rng2.Cells(j, 1).Interior.ColorIndex = 0
    End If
    
    
    
Next
rng2.Cells(1, 1).Select
End Sub



Private Function isHoliday(tempDate1 As Date, japanHolidays1() As Date) As Boolean
Dim k As Integer
isHoliday = False
For k = 1 To UBound(japanHolidays1)
If (japanHolidays1(k) = tempDate1) Then
isHoliday = True
Exit For
End If
Next
End Function



Private Function shouldMarked(tempDate1 As Date, validateDates1() As Date) As Boolean
Dim q As Integer
shouldMarked = False
For q = 1 To UBound(validateDates1)
If (validateDates1(q) = tempDate1) Then
shouldMarked = True
Exit For
End If
Next
End Function