vba - 根据列标题和日期格式突出显示单元格

Question

数据

目的是根据列标题突出显示非日期单元格。
(突出显示屏幕截图单元格 C3、c5、D2、D6)

下面的代码我尝试达到目的但失败了。请帮忙看看我可以改变什么？

Sub colortest()
    Dim MyPage As Range, currentCell As Range

    With Sheets(2).Rows(1)
        Set t = .Find("Cut Date", lookat:=xlPart)
        Set A = Columns(t.Column).EntireColumn

        For Each currentCell In A
            If Not IsEmpty(currentCell) Then
                Select Case Not IsDate(currentCell.Value)
                    Case 1
                        currentCell.Interior.Color = 56231
                End Select
            End If
        Next currentCell
    End With
End Sub

Answer 1

或者

Option Explicit

Public Sub colortest()
    Dim MyPage As Range, currentCell As Range, t As Range, findString As String
    findString = "Date"

    With ThisWorkbook.Worksheets("Sheet2")

        Set t = .Rows(1).Find(findString, LookAt:=xlPart)

        Dim currMatch As Long

        For currMatch = 1 To WorksheetFunction.CountIf(.Rows(1).Cells, "*" & findString & "*")

            Set t = Rows(1).Find(What:=findString, After:=t, _
                                 LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                 SearchDirection:=xlNext, MatchCase:=False)

            If t Is Nothing Then Exit Sub

            For Each currentCell In Intersect(.Columns(t.Column), .UsedRange.Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count).Offset(1, 0))
                If Not IsEmpty(currentCell) And Not IsDate(currentCell.Value) Then currentCell.Interior.Color = 56231
            Next currentCell

        Next currMatch
    End With
End Sub