无视空白细胞

Question

我已经编写了一个代码，它完全符合我的要求，但它也改变了匹配空白的颜色。我想知道我能在代码中添加什么，这样空白单元格就不会被标记。

beginrow = 2
First = 0
FirstLast = 0
Second = 0
SecondLast = 0

For z = 2 To finalrow
    If Cells(z, 9).Value = Cells(1 + z, 9).Value And Cells(z, 10).Value <> Cells(1 + z, 10).Value And Cells(z, 12).Value = Cells(1 + z, 12).Value Then
        First = z
        FirstLast = First + 1
    End If
    If Cells(z, 9).Value = Cells(1 + z, 9).Value And Cells(z, 12).Value <> Cells(1 + z, 12).Value Then
            Second = z
            SecondLast = Second + 1
            endKnown = True
    End If
    If endKnown = True Then
        For arownumber = beginrow To First 'need to find the rownumbers that we compare with
            For change = 4 To 7
                For smrownumber = FirstLast To Second 'need to find the rownumbers for comparing
                    For across = 4 To 7
                        CellA = Cells(arownumber, change)
                        CellB = Cells(smrownumber, across)
                        match = IIf(CellA = CellB, "yes", "no")
                        If match = "yes" Then
                            Cells(arownumber, change).Interior.ColorIndex = 3
                            Cells(smrownumber, across).Interior.ColorIndex = 3
                        End If
                    Next across
                Next smrownumber
            Next change
        Next arownumber
        endKnown = False
        If SecondLast <> 0 Then
            beginrow = SecondLast
        End If
      End If
 Next z

Answer 1

在更改ColorIndex对象的Interior属性之前，必须检查是否为空内容。

'If your cell isn't empty then change background color
If(Cells(arownumber, change).Value <> "") Then
    Cells(arownumber, change).Interior.ColorIndex = 3
End If

'If your cell isn't empty then change background color
If(Cells(smrownumber, across).Value <> "") Then
    Cells(smrownumber, across).Interior.ColorIndex = 3
End If

由于Cells必须在执行条件之前进行匹配，所以可以使用以下内容来简化事情：

'If your cell isn't empty then change background color
If(Cells(arownumber, change).Value <> "" And Cells(smrownumber, across).Value <> "") Then
    Cells(arownumber, change).Interior.ColorIndex = 3
    Cells(smrownumber, across).Interior.ColorIndex = 3
End If

Answer 2

你是说像这样？

match = IIf((CellA = CellB) And (CellA <> ""), "yes", "no")

而不是

match = IIf(CellA = CellB, "yes", "no")