VBA-Code does not recognize 0.5 when comparing columns + misplaced highlights

[bpetro]

Hello everybody,

I have code that compares the values in column D and G. When there is a difference between the two values, the value in column D is highlighted red. There are other criteria that count "acceptable" and will not highlight. These include if column G is N/A and Column D is 1 and if column G is N/A and column D is 0.

When I have been running some testing I have noticed two issues.

1. Whenever there is a 0.50 in column D, the code does not pick up that there is a difference, no matter what is in Column G on the same row.
2. Some of the highlights are put in column G but I just want column D highlighted when there is a difference.

Option Explicit
Public Sub RateTest()
    Dim ws As Worksheet, miss As Range, tmp As Range, t As Double
    Dim max1 As Long, max2 As Long, colD As Range, colG As Range
    t = Timer
    Set ws = ThisWorkbook.Sheets("Basic Annual Premium")
    max1 = ws.Cells(Rows.Count, "D").End(xlUp).Row
    max2 = ws.Cells(Rows.Count, "G").End(xlUp).Row
    Set colD = ws.Range(ws.Cells(2, "D"), ws.Cells(max1, "D"))
    Set colG = ws.Range(ws.Cells(2, "G"), ws.Cells(max2, "G"))
    colD.Interior.ColorIndex = xlColorIndexNone
    colG.Interior.ColorIndex = xlColorIndexNone
    Set miss = CheckColumns(colD, colG, "N/A")
    If miss Is Nothing Then
        Set miss = CheckColumns(colG, colD, "1")
    Else
        Set tmp = CheckColumns(colG, colD, "1")
        If Not tmp Is Nothing Then Set miss = Union(miss, tmp)
    End If
    If Not miss Is Nothing Then miss.Interior.Color = RGB(255, 0, 0)
    Debug.Print "Rows: " & max1 & "; Time: " & Format(Timer - t, "0.000") & " sec"
End Sub

Private Function CheckColumns(col1 As Range, col2 As Range, x As String) As Variant
    Dim c As Variant, r As Long, d As Object, rng As Range
    c = col1.Value2
    Set d = CreateObject("Scripting.dictionary")
    For r = 1 To UBound(c)
       d(CStr(c(r, 1))) = vbNullString
    Next
    c = col2.Value2
    For r = 1 To UBound(c)
        If Len(c(r, 1)) > 0 Then
            If c(r, 1) <> x Then
                If Not d.exists(CStr(c(r, 1))) Then
                    If rng Is Nothing Then
                        Set rng = col2.Cells(r)
                    Else
                        Set rng = Union(rng, col2.Cells(r))
                    End If
                End If
            End If
        End If
    Next
    Set CheckColumns = rng
End Function

Thank you for your consideration and thanks ahead of time to all that reply.

 

[cerfani]

i would advise stepping through the code with a small data set and tell us which line fails and what values it is testing

 

[Joe4]

What exactly is "x" (the third argument in your Function) supposed to be?
If it should be a numeric value, it should not be declared as a String or set to "1" (which is actually the text value of "1", not the numeric value 1).

 

[bpetro]

What exactly is "x" (the third argument in your Function) supposed to be?
If it should be a numeric value, it should not be declared as a String or set to "1" (which is actually the text value of "1", not the numeric value 1).

Sorry, I am sharing this code with a partner and he came up with it, but I know it keeps the "N/A" values from returning red correctly. If placed a "1", the code highlights the "N/A"'s when they are paired with a 1 in column D which is incorrect (should not highlight red).

 

[bpetro]

i would advise stepping through the code with a small data set and tell us which line fails and what values it is testing

How is this done? I know how to step into the code but I do not know what I am looking for.

 

[Joe4]

How is this done? I know how to step into the code but I do not know what I am looking for.

As you come to each calculation, hover over the variable name to see what the value is.
And you can see which path it takes in your IF statements.
So you should be able to verify if the calculations look right and it is taking the correct path.
Basically, you are just following the data through the whole process, and should be able to see where things take a wrong turn.

 

[cerfani]

you have to set breakpoints in your code so when the execution gets to that line it will pause. Then when it is paused you can press F8 each time to execute each line. Also variables like strings and numbers you can read the value by mousing over the variable while the execution is paused. So manually step through your code and then tell us here which line fails. When it fails, mouse over the variables in that line and tell us their values. Then people might know what is wrong.