Ok, I have been trying to fix this for several weeks to no avail. I am building a calendar in excel that will change the colors of the days depending on data on another sheet within the same workbook. The VB script is supposed to iterate through the range of cells that make up the calendar and first check if it is a date. If it is, it takes that value and performs a vlookup to the other sheet for all of the dates and is supposed to return the corresponding row regarding if the person worked or not. The problem is that the vlookup function always returns the error 2042. I changed the variable to variant so I can catch it the error but I shouldn't be receiving one in the first place. Every date for the year is in this column but it always says it can't find it. The formatting for the dates are different, but to the best of my knowledge, that shouldn't matter. The code is below.
I also attempted the Lookup function with the same problem. I tried the vlookup function as a regular formula and it works, but it will not work from VB. I also tried doing the function from the same sheet instead of from a different sheet with no success. Any help would be appreciated.
Code:
Private Sub cmdCalendar_Click()
Dim myRange As Range
Dim varName As Variant
Dim intColor As Integer
Dim cell As Range
Set myRange = Range("$C$3:$AJ$24")
For Each cell In myRange.Cells
If IsDate(cell.Value) = True Then
varName = Application.VLookup(cell.Value, Sheet1.Range("$A$6:$G$371"), 7, False)
If Not IsError(varName) Then
Select Case varName
Case "Working"
intColor = Range("$F$26").Interior.ColorIndex
Case "RDO"
intColor = Range("$F$27").Interior.ColorIndex
Case "Chart Day"
intColor = Range("$F$28").Interior.ColorIndex
Case "Vacation Day"
intColor = Range("$F$29").Interior.ColorIndex
Case "Sick Day"
intColor = Range("$F$30").Interior.ColorIndex
Case "Military Day"
intColor = Range("$F$31").Interior.ColorIndex
Case "Lost Time"
intColor = Range("$F$32").Interior.ColorIndex
End Select
cell.Interior.ColorIndex = intColor
End If
End If
Next cell
End Sub
I also attempted the Lookup function with the same problem. I tried the vlookup function as a regular formula and it works, but it will not work from VB. I also tried doing the function from the same sheet instead of from a different sheet with no success. Any help would be appreciated.