I am getting a Type Mismatch error on - Case "1" in the following code: Private Sub…
- User
I am getting a Type Mismatch error on - Case "1" in the following code: Private Sub Worksheet_Activate() Application.EnableEvents = False Dim ws As Worksheet Dim cell As Range Set ws = ThisWorkbook.Sheets("All Checks") ws.Unprotect password:="edit" For Each cell In ws.Range("A2:T55") 'If cell.value <> "" And cell.Font.Size <> 8 Then On Error Resume Next If cell.value <> "" Then cell.Font.Bold = False cell.Font.Size = 8 End If Next cell On Error GoTo 0 ws.Range("AA2:AA23").Calculate ws.Range("AB2:AB12").Calculate ws.Range("AC2:AC6").Calculate ws.Range("AD2:AD13").Calculate ws.Range("AE2:AE20").Calculate ws.Range("AF2:AF12").Calculate ws.Range("AG2:AG54").Calculate ws.Range("AH2:AH3").Calculate ws.Range("AI2").Calculate ws.Range("AJ2:AJ5").Calculate
ws.Range("B2:B24").Calculate ws.Range("D2:D13").Calculate ws.Range("F2:F7").Calculate ws.Range("H2:H14").Calculate ws.Range("J2:J21").Calculate ws.Range("L2:L13").Calculate ws.Range("N2:N55").Calculate ws.Range("P2:P4").Calculate ws.Range("R2:R3").Calculate ws.Range("T2:T6").Calculate
Dim columnToCheck2, _ columnToCheck4, _ columnToCheck6, _ columnToCheck8, _ columnToCheck10, _ columnToCheck12, _ columnToCheck14, _ columnToCheck16, _ columnToCheck18, _ columnToCheck20 As Long Dim lastRow As Long columnToCheck2 = 2 columnToCheck4 = 4 columnToCheck6 = 6 columnToCheck8 = 8 columnToCheck10 = 10 columnToCheck12 = 12 columnToCheck14 = 14 columnToCheck16 = 16 columnToCheck18 = 18 columnToCheck20 = 20 Dim i As Integer lastRow = Cells(Rows.count, columnToCheck2).End(xlUp).Row ' colour change for Day Checks For i = 1 To lastRow With Cells(i, columnToCheck2) ' Column B Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck4).End(xlUp).Row ' colour change for Weekly Checks For i = 1 To lastRow With Cells(i, columnToCheck4) ' Column D Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck6).End(xlUp).Row ' colour change for Forthnightly Checks For i = 1 To lastRow With Cells(i, columnToCheck6) ' Column F Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck8).End(xlUp).Row ' colour change for Monthly Checks For i = 1 To lastRow With Cells(i, columnToCheck8) ' Column H Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck10).End(xlUp).Row ' colour change for Termly Checks For i = 1 To lastRow With Cells(i, columnToCheck10) ' Column J Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck12).End(xlUp).Row ' colour change for 6mth Checks For i = 1 To lastRow With Cells(i, columnToCheck12) ' Column L Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck14).End(xlUp).Row ' colour change for 12mth Checks For i = 1 To lastRow With Cells(i, columnToCheck14) ' Column N Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck16).End(xlUp).Row ' colour change for 2yr Checks For i = 1 To lastRow With Cells(i, columnToCheck16) ' Column P Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck18).End(xlUp).Row ' colour change for 3yr Checks For i = 1 To lastRow With Cells(i, columnToCheck18) ' Column R Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next i lastRow = Cells(Rows.count, columnToCheck20).End(xlUp).Row ' colour change for 5yr Checks For i = 1 To lastRow With Cells(i, columnToCheck20) ' Column T Select Case .value Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End With Next iApplication.EnableEvents = True ThisWorkbook.Sheets("All Checks").Protect password:="edit" ' SCROLL THE PAGE Call StartAutoScroll
End Sub
- Assistant
The Type Mismatch error in your code likely occurs because the value being compared in the
Select Caseconstruct may not be a string. Here are a few steps to troubleshoot and fix the issue:Fixing Type Mismatch in Select Case
-
Ensure Comparison with Strings: Since you are using string comparisons (e.g.,
Case "1"), you should ensure that the cell values being checked are indeed strings. A direct numeric value check can trigger type mismatch errors. You can convert the cell value to a string explicitly using theCStrfunction. -
Check for Empty Cells: When the cell is empty or contains an unexpected data type, it can also trigger a type mismatch. Check for IsEmpty or ensure the value is suitable for comparison.
Modified Code Example
Here's how you might modify the relevant sections of your code to avoid the issue:
With Cells(i, columnToCheck2) ' Column B If Not IsEmpty(.Value) Then Select Case CStr(.Value) ' Convert to string explicitly Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End If End WithApply to Other Sections
You'll want to apply similar modifications to all instances of the
Select Casestatements throughout your procedures. For instance, replace:Select Case .Valuewith
Select Case CStr(.Value)Full Example for One Block
Here’s how the first block could look with the necessary checks:
lastRow = Cells(Rows.Count, columnToCheck2).End(xlUp).Row ' Check for Day Checks For i = 1 To lastRow With Cells(i, columnToCheck2) ' Column B If Not IsEmpty(.Value) Then Select Case CStr(.Value) ' Convert to string for comparison Case "1" .Offset(0, -1).Interior.Color = RGB(0, 255, 0) ' Green .Offset(0, -1).Font.Color = vbBlack Case "2" .Offset(0, -1).Interior.Color = RGB(255, 153, 0) ' Orange .Offset(0, -1).Font.Color = vbBlack Case "3" .Offset(0, -1).Interior.Color = RGB(255, 0, 0) ' Red .Offset(0, -1).Font.Color = vbWhite End Select End If End With Next iSummary
- Always convert cell values to the expected type when performing comparisons, particularly strings.
- Check for empty cells or unexpected data types before comparing values to prevent type mismatch errors.
Following these practices should help eliminate the type mismatch error you are encountering.
-