核对不同数据的显示颜色

志65

hi, 我想要核对2个区域指定的数据，若是不同的标黄色。

如截图，这有2个区域的数据。如H列区域的AA-Auto跟N列AA-Auto核对他们点击数和花费，即I列和O列比较，J列和P列比较。
可我运行代码，不知为何J列和P列的数据一样，也会显示颜色，相同的不应该显示颜色，但是这不知哪里出了问题。多谢

Sub 核对数据()
Dim r, rp, col, g, y As Byte
Dim d, dr As Object

Cells.Interior.Color = xlNone
r = Cells(Rows.Count, "n").End(xlUp).Row
rp = Cells(Rows.Count, "h").End(xlUp).Row
Set d = CreateObject("scripting.dictionary")
Set dr = CreateObject("scripting.dictionary")
For y = 2 To r
    d(Cells(y, "n").Value) = Cells(y, "o").Value
    dr(Cells(y, "n").Value) = Cells(y, "p").Value
Next

For y = 2 To rp - 1
    If d(Cells(y, "h").Value) <> Cells(y, "i").Value Then Cells(y, "h").Interior.Color = vbYellow
    If dr(Cells(y, "h").Value) <> Cells(y, "j").Value Then Cells(y, "h").Interior.Color = vbYellow
Next
MsgBox r & Chr(10) & rp
End Sub

志65

我知道问题所在了，因为小数的问题，不知是不是数字型有的问题还是怎样。我是把有小数的列以“.”来分列，然后在用&字符连接起来，再运行我的代码就可以了。

志65

已解决

Sub 核对数据()
Dim r, rp, col, y As Byte
Dim d, dr As Object
Dim a, b, c, f, g, h As Byte

Application.FindFormat.Clear
a = Cells.Find("源数据", lookat:=xlWhole).Column
b = Cells.Find("Click", lookat:=xlWhole).Column
c = Cells.Find("Spend", lookat:=xlWhole).Column
f = Cells.Find("Campaigns", lookat:=xlWhole).Column
g = Cells.Find("Clicks", lookat:=xlWhole).Column
h = Cells.Find("Spend(USD)", lookat:=xlWhole).Column

Cells.Interior.Color = xlNone
r = Cells(Rows.Count, f).End(xlUp).Row
rp = Cells(Rows.Count, a).End(xlUp).Row
Set d = CreateObject("scripting.dictionary")
Set dr = CreateObject("scripting.dictionary")
For y = 2 To r
    If InStr(Cells(y, h), ".") Then Cells(y, h) = Split(Cells(y, h).Value, ".")(0) & "." & Split(Cells(y, h).Value, ".")(1)
    d(Cells(y, f).Value) = Cells(y, g).Value
    dr(Cells(y, f).Value) = Cells(y, h).Value
Next

For y = 2 To rp - 1
    If InStr(Cells(y, c), ".") Then Cells(y, c) = Split(Cells(y, c).Value, ".")(0) & "." & Split(Cells(y, c).Value, ".")(1)
    If d(Cells(y, a).Value) <> Cells(y, b).Value Then Cells(y, a).Interior.Color = vbYellow
    If dr(Cells(y, a).Value) <> Cells(y, c).Value Then Cells(y, a).Interior.Color = vbYellow
Next
MsgBox r & Chr(10) & rp
End Sub