Compare numeric value of cells that contain number and text

Hi, I'm trying to highlight the cells if the cells in column N (max qty) is less than column M (min qty), if the cells contains an integer.
I have tried the code below but it also highlights cases where the cells consists of numbers + text (eg 10 boxes) How do I tweak it such that it reads only the numeric value of the cell and compares between M and N? If that's not possible, I will hope to at least ignore such rows
Dim r As Integer
Dim Mmin As Integer
Dim Mmax As Integer
For r = 4 To LastRow
    mmin = Cells(r, 13).Value
    Mmax = Cells(r, 14).Value
    If IsNumeric(mmin) And IsNumeric(Mmax) Then
    If Mmax < mmin Then
    Cells(r, 13).Interior.ColorIndex = 6
    Cells(r, 14).Interior.ColorIndex = 6
End If
End If

Next

Open in new window

Pearlyn TanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
Dim strMin() As String, strMax() As String
For r = 4 To LastRow
    strMin = Split(Cells(r, 13).Value, " ")
    strMax = Split(Cells(r, 14).Value, " ")
    If IsNumeric(strMin(0)) And IsNumeric(strMax(0)) Then
        If strMax(0) > strMin(0) Then
            Cells(r, 13).Interior.ColorIndex = 6
            Cells(r, 14).Interior.ColorIndex = 6
        Else
            Cells(r, 13).Interior.ColorIndex = xlNone
            Cells(r, 14).Interior.ColorIndex = xlNone
        End If
    End If
Next

Open in new window

Pearlyn TanAuthor Commented:
Hi Neeraj, thanks for the solution. I have replaced my code.
Dim r As Integer
Dim Mmin As Integer
Dim Mmax As Integer

Dim strMin() As String, strMax() As String
For r = 4 To LastRow
    strMin = Split(Cells(r, 13).Value, " ")
    strMax = Split(Cells(r, 14).Value, " ")
    If IsNumeric(strMin(0)) And IsNumeric(strMax(0)) Then
        If strMax(0) > strMin(0) Then
            Cells(r, 13).Interior.ColorIndex = 6
            Cells(r, 14).Interior.ColorIndex = 6
        Else
            Cells(r, 13).Interior.ColorIndex = xlNone
            Cells(r, 14).Interior.ColorIndex = xlNone
        End If
    End If
Next

Open in new window

The rows with numbers + text are now not highlighted, however I have random rows that are correct (min < max) being highlighted. About 70% of the rows that have min<max are now highlighted, not sure what went wrong?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please upload a sample workbook with data in columns M and N to find out why it doesn't work.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

aikimarkCommented:
If a cell contains both text and numbers, it doesn't have a 'numeric value'.
If an alphanumeric string contains non-digit characters, it will cause a False value to be returned by IsNumeric().
Does the cell text include only a single numeric value or might there be multiple digit groups?
You could use this function to strip away all non-digit characters from the cell value before your processing logic.
Public Function OnlyDigits(ByVal parmCellText) As String
    Static oRE As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "\D"
    End If
    OnlyDigits = oRE.Replace(parmCellText, vbNullString)
End Function

Open in new window

So, in your code, you would implement this function in these two statements:
    mmin = OnlyDigits(Cells(r, 13).Value)
    Mmax = OnlyDigits(Cells(r, 14).Value)

Open in new window

Pearlyn TanAuthor Commented:
Sample workbook screenshot
Thanks Aikimark for your input. While the rows with texts in them are not highlighted, the other rows that have error (max < min) are also not highlighted. I'm not sure what's wrong. Attached a screenshot of my workbook of columns M and N..
aikimarkCommented:
Since the text (units) always seems to follow the number, you could use the Val() function instead of OnlyDigits().

You have a problem with units appearing in your min/max columns.  You have some work ahead of you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay give it a try and see if you get the desired output...
Dim r As Integer
Dim Mmin As Integer
Dim Mmax As Integer
For r = 4 To LastRow
    Mmin = Val(Cells(r, 13))
    Mmax = Val(Cells(r, 14))
    If Mmin > 0 And Mmax > 0 Then
        If Mmax < Mmin Then
            Cells(r, 13).Interior.ColorIndex = 6
            Cells(r, 14).Interior.ColorIndex = 6
        Else
            Cells(r, 13).Interior.ColorIndex = xlNone
            Cells(r, 14).Interior.ColorIndex = xlNone
        End If
    End If
Next

Open in new window

Here is the output based on your sample data. The cells are highlighted based on condition Mmax < Mmin.
MinMax.jpg

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pearlyn TanAuthor Commented:
Thank you both! Val function worked perfectly.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Pearlyn! Glad we could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.