CODEWITHSUNDEEP
vbaexcelconditional-formatting
Jenny Dithe
Jenny Dithe

Reputation: 11

VBA Error 13 type: mismatch

I am not very familiar with VBA but needed to change my excel to allow more than 3 conditional formattings.

I found the below code online and want to change the colour of the cell depending on the content with a choice of six different values.

My code is:

Private Sub Worksheet_Change(ByVal Target As Range)
  Set MyPlage = Range("G3:AG115")

   For Each Cell In MyPlage
     If Cell.Value = "." Then
      Cell.Interior.ColorIndex=28
      Cell.Font.Bold = True
     End If

     If Cell.Value = "X1" Then
       Cell.Interior.ColorIndex=32
       Cell.Font.Bold = True
     End If

     If Cell.Value = "1X" Then
       Cell.Interior.ColorIndex=6
       Cell.Font.Bold = True
     End If

     If Cell.Value = "2X" Then
       Cell.Interior.ColorIndex=45
       Cell.Font.Bold = True
     End If

     If Cell.Value = "3X" Then
       Cell.Interior.ColorIndex=4
       Cell.Font.Bold = True
     End If

     If Cell.Value = "XY" Then
       Cell.Interior.ColorIndex=44
       Cell.Font.Bold = True
     End If

     If Cell.Value = "bt" Then
       Cell.Font.ColorIndex=27
       Cell.Interior.ColorIndex=27
     End If

     If Cell.Value = "bl" Then
       Cell.Font.ColorIndex=28
       Cell.Interior.ColorIndex=28
     End If

     If Cell.Value <> "bt" And Cell.Value <> "bl" And Cell Value <> "." And Cell.Value <> "X1" And Cell.Value <> "1X" And Cell.Value <> "2X" And Cell.Value <> "3X" And Cell.Value <> "XY" Then
       Cell.Interior.ColorIndex=xlNone
     End If
   Next
End Sub

The content is chosen either from a drop down list or else the bt and bl are written in to check that these rows are highlighted.

When I try and change the content I get Error: 13 Type Mismatch.

The line 

If Cell.Value = "." Then

is highlighted as the error source (I thought the problem might be with the "." but if I remove that set of instructions then the line

If Cell.Value = "X1" Then

is highlighted)

I have googled and seen that you can do a loop that if error Then Next, I'm not sure how I would code this exactly and I would rather solve the coding problem than do a quick fix.

If anyone has any ideas on where I am going wrong / solutions that would be great.

Upvotes: 1

Views: 4984

Answers (1)

chris neilsen
chris neilsen

Reputation: 53127

EDIT

If you have any error values in the sheet (eg #NA #DIV/0 etc) then the If Cell... line will fail

Change it to

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell as Range
    Set MyPlage = Range("G3:AG115")
    For Each Cell In MyPlage.Cells
        If Not IsError(Cell) Then
            If Cell.Value = "." Then
                Cell.Interior.ColorIndex=28
                Cell.Font.Bold = True
            End If

            etc

        End If
     Next

Upvotes: 2

Related Questions