Thread: Confusing Variable Types

I have written some code (below) that will search through specific cells that contain dates formatted at DD-MM-YYYY and will highlight that cell and the two cells above it if the date represented is before the current date. There is an if statement that checks to see if the years are the same and if one of the days is greater than the other. for example, if today is the 28th, and the day value from the cell is 27, then that day is passed and the cells get highlighted Everything works fine, until there is a date in one of those cells whose month is 10 (october), 11(november) or december (12). . i am thinking it has something to do with the way i check the dates (string functions to pull out the different values).

the part in the code where i believe the problem lies is in bold. the problem is that one value might be 6, and the other 10 but when excel checks to see if 6 is greater than 10, it accepts that as true and continues on, even though that is clearly false.

thanks in advance for the help

Public Sub HighlightCells()
Dim dates As Range, cell As Range
Dim strvalue As String
Dim monthnow1, daynow, yearnow As Single, monthnow As String
Dim monthcell, daycell, yearcell, monthcell1 As Single
Dim i  As Integer


Count = 13
For i = 13 To 55 Step 3
Range("C" & i, "N" & i).Name = "dates"
For Each cell In Range("dates")
    If cell.Value <> "" Then
        strvalue = Left(Now(), InStr(Now(), " ") - 1)
        monthnow = Val(Left(strvalue, InStr(strvalue, "/") - 1))
        daynow = InStr(strvalue, "/")
        daynow = Right(strvalue, Len(strvalue) - daynow)
        yearnow = Right(daynow, Len(daynow) - InStr(daynow, "/"))
        daynow = Left(daynow, InStr(daynow, "/") - 1)
    
        strvalue = cell.Value
        monthcell = Val(Left(strvalue, InStr(strvalue, "/") - 1))
        daycell = InStr(strvalue, "/")
        daycell = Right(strvalue, Len(strvalue) - daycell)
        yearcell = Right(daycell, Len(daycell) - InStr(daycell, "/"))
        daycell = Left(daycell, InStr(daycell, "/") - 1)
    
            If yearnow > yearcell Then
                With Range(cell.Address, cell.Offset(-2))
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                    End With
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                    End With
                End With
                
            End If
            If yearnow = yearcell And monthnow > monthcell Then                
                With Range(cell.Address, cell.Offset(-2))
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                    End With
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                    End With
                End With
        End If
           
            
       If yearnow = yearcell And monthnow = monthcell And daynow > daycell Then
                With Range(cell.Address, cell.Offset(-2))
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                    End With
                
                    With .Font
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    End With
                End With
        End If
    End If
  
    monthnow = 0
    monthcell = 0
    yearnow = 0
    yearcell = 0
    daynow = 0
    daycell = 0
Next cell
Next i
    
End Sub

Attached is a copy of the Excel Sheet