VBA Greater Than function is actually showing less than answers

Question

I am trying to write a piece of code to allow Excel to run through lines of data and automatically highlight those lines that need to be highlighted.

Copy of my data

Cells G, H, and I are all formulas. G10 would equal C10/B10, H10 would equal D10/B10, and I10 would equal E10/B10.

Sub HighlightRows()

On Error Resume Next

Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
rowcount = Selection.Rows.Count
Range("A10").Select



For mycounter = 1 To rowcount

    Ret1 = FormatPercent(ActiveCell.Offset(0, 6).Value, 2, vbTrue)
    Ret2 = FormatPercent(ActiveCell.Offset(0, 7).Value, 2, vbTrue)
    Ret3 = FormatPercent(ActiveCell.Offset(0, 8).Value, 2, vbTrue)

    If ActiveCell.Offset(0, 6).Value = "#DIV/0!" Then
        ActiveCell.Offset(1, 0).Select

    ElseIf Ret1 >= "0.50%" Then

        Selection.EntireRow.Interior.Color = 65535
        ActiveCell.Offset(1, 0).Select

    ElseIf Ret2 >= "3.00%" Then

        Selection.EntireRow.Interior.Color = 65535
        ActiveCell.Offset(1, 0).Select

    ElseIf Ret3 > "15.00" Then

        Selection.EntireRow.Interior.Color = 65535
        ActiveCell.Offset(1, 0).Select


    ElseIf Ret3 = "15.00%" Then

        Selection.EntireRow.Interior.Color = 65535
        ActiveCell.Offset(1, 0).Select

    Else

        ActiveCell.Offset(1, 0).Select


    End If


Next

End Sub

That is all of the code that I have for this particular module. I broke apart the Ret1, Ret2, and Ret3 because I needed to find which one of them was causing trouble.

My problem at the current moment is with ElseIF Ret3 > "15.00%". If you look at the picture of my data cell I13 is where the issue lays.

When I run through the code in VBA it show Ret3 as "4.21%" but when it gets to the line of code where Ret3 > "15.00%" it highlights the row.

I've searched online but I can't find anything that has helped. I've played around with the operator but I can't get it to work correctly. Normally I find an answer that I need on StackOverflow before I've ever had to ask a question but I just haven't found anything that has fixed the issue consistently throughout the entire data set.

Strings are not numbers. When comparing strings "100" < "99" is true, because it's alphabetical order not numerical order. — Ben

Ben Ben · Accepted Answer · 2019-03-18T18:16:42

Strings are not numbers. When comparing strings "100" < "99" is true, because it's alphabetical order not numerical order, and 1 comes before 9.

Therefore rather than converting all your numbers to strings before comparing them, it would be better to convert all your strings to numbers.


Option Explicit

Sub HighlightRows()

    On Error Resume Next

    Range("A10").Select
    Range(Selection, Selection.End(xlDown)).Select
    rowcount = Selection.Rows.Count
    Range("A10").Select



    For mycounter = 1 To rowcount

        Dim Ret1 Double
        Dim Ret2 Double
        Dim Ret3 Double
        Ret1 = CDbl(ActiveCell.Offset(0, 6).Value)
        Ret2 = CDbl(ActiveCell.Offset(0, 7).Value)
        Ret3 = CDbl(ActiveCell.Offset(0, 8).Value)

        If ActiveCell.Offset(0, 6).Value = "#DIV/0!" Then
            ActiveCell.Offset(1, 0).Select

        ElseIf Ret1 >= 0.005 Then

            ' and so on...

And so on.

VBA Greater Than function is actually showing less than answers

1 Answers