Ndtyjky

I'm trying to setup a simple solution to highlight cells that have a date that is greater than today's date. What I currently have is the following:

Sub Test()

  Dim lrow As Long

  lrow = Cells(Rows.Count, 1).End(xlUp).Row

  Columns("H:H").EntireColumn.AutoFit

  If Range("H2:H" & lrow).Value > Date Then Cell.Interior.Color = vbYellow

End Sub

I keep getting a "Type Mismatch" erroer but the column (H) is formatted as Date so I'm not sure what I'm doing wrong. Any help or direction would be greatly appreciated.

In response to @MatthieuGuindon's suggestion to @CharlesPL's answer, here's some code that does the conditional formatting. I've set it so it highlights dates that are after the day you run it as a bright yellow.

Option Explicit



Sub setCondFormat()

Dim lrow As Long



lrow = ActiveSheet.Range("H" & ActiveSheet.Rows.Count).End(xlUp).Row

With Range("H2:H" & lrow)

    .FormatConditions.Delete

    .FormatConditions.Add Type:=xlExpression, Formula1:= _

    "=H2>TODAY()"

    With .FormatConditions(.FormatConditions.Count)

        .SetFirstPriority

        With .Interior

            .PatternColorIndex = xlAutomatic

            .Color = 65535

            .TintAndShade = 0

        End With

    End With

End With



End Sub

Range("H2:H" & lrow).Value will be a 2D array (the Value of a Range is always a 2D array if more than a single cell is involved); you're getting a type mismatch error because you can't compare a 2D array to a Date; if you can't use a conditional formatting, you need to compare the individual array subscripts.

Last thing you want to do is to iterate each individual cells (otherwise your next question will be "how do I make this loop run faster?"). Get that array into a Variant, and iterate that array - since it's only 1 column, make it a 1D array with Application.Transpose:

Dim values As Variant

values = Application.Transpose(Range("H2:H" & lastRow).Value)



Dim i As Long, current As Long

For i = LBound(values) To UBound(values)

    current = i + 1 'array would be 1-based, so to start at row 2 we need to offset by 1

    If values(i) > Date Then

        ActiveSheet.Cells(current, 8).Interior.Color = vbYellow

    End If

Next

That way you only hit the worksheet when you have to.

Use conditional formatting! As the name suggests, this is build for that!

Microsoft blog post on date conditional formating

I would recommend iterating over the range of cells and testing each cell individually. Please see below.

Dim rng As Range, cell As Range

Set rng = Range("H:H")



For Each cell In rng

   If cell.Value > Date Then cell.Interior.Color = vbYellow

Next cell