VBA错误13类型：不匹配 [英] VBA Error 13 type: mismatch

问题描述

我不太熟悉VBA，但需要更改我的excel以允许超过3种条件格式。

我在线找到以下代码，并希望更改

我的代码是：

  Private Sub Worksheet_Change（ByVal Target As Range）
 Set MyPlage = Range（G3：AG115）
 
 MyPlage中的每个单元格
如果Cell.Value =。然后
 Cell.Interior.ColorIndex = 28 
 Cell.Font.Bold = True 
如果
 
如果Cell.Value =X1，则
 Cell.Interior.ColorIndex = 32 
 Cell.Font.Bold = True 
如果
 
如果Cell.Value =1X则
 Cell.Interior.ColorIndex = 6 
 Cell.Font.Bold = True 
如果
 
如果Cell.Value =2X则
 Cell.Interior.ColorIndex = 45 
 Cell.Font.Bold = True 
 End If 
 
如果Cell.Value =3X然后
 Cell.Interior.ColorIndex = 4 
 Cell.Font。 Bold = True 
如果
 
如果Cell.Value =XY则
 Cell.Interior.ColorIndex = 44 
 Cell.Font.Bold = True 
 End If 
 
如果Cell.Value =bt然后
 Cell.Font.ColorIndex = 27 
 Cell.Interior.ColorIndex = 27 
 End If 
 
如果Cell.Value =bl然后
 Cell.Font.ColorIndex = 28 
 Cell.Interior.Colo rIndex = 28 
 End If 
 
如果Cell.Value<> bt和Cell.Value<> bl和单元格值 和Cell.Value<> X1和Cell.Value 1X和Cell.Value 2X和Cell.Value 3X和Cell.Value<> XY然后
 Cell.Interior.ColorIndex = xlNone 
结束If 
下一个
 End Sub 
  

内容是从下拉列表中选择的，否则bt和bl被写入，以检查这些行是否突出显示。

当我尝试更改内容时，我得到错误：13类型不匹配。

p>

 如果Cell.Value =。然后
  

突出显示为错误源（我认为问题可能与。，但如果我删除那套说明，那么行

 如果Cell.Value =X1然后
  

被突出显示）

我已经google了，看到你可以做一个循环，如果错误 Then Next ，我不知道我将如何编写这个，我宁愿解决编码问题比快速修复。

如果任何人有任何想法，我会在哪里错过/解决方案将是伟大的。

表格中有任何错误值（例如

code> #NA ＃DIV / 0 等）然后如果单元格... 行将失败

将其更改为

  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim Cell as Range 
 Set MyPlage = Range（G3：AG115 ）
对于MyPlage.Cells中的每个单元格
如果不是IsError（单元格）然后
如果Cell.Value =。然后
 Cell.Interior.ColorIndex = 28 
 Cell.Font.Bold = True 
如果
 
 etc 
 
结束If 
下一个
  

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.

解决方案

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

这篇关于VBA错误13类型：不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！