Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

I am getting a Type Mismatch error on - Case "1" in the following code: Private Sub…

  1. 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 i
    

    Application.EnableEvents = True ThisWorkbook.Sheets("All Checks").Protect password:="edit" ' SCROLL THE PAGE Call StartAutoScroll

    End Sub

  2. Assistant

    The Type Mismatch error in your code likely occurs because the value being compared in the Select Case construct may not be a string. Here are a few steps to troubleshoot and fix the issue:

    Fixing Type Mismatch in Select Case

    1. 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 the CStr function.

    2. 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 With
    

    Apply to Other Sections

    You'll want to apply similar modifications to all instances of the Select Case statements throughout your procedures. For instance, replace:

    Select Case .Value
    

    with

    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 i
    

    Summary

    • 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.