asked on

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

Subodh Tiwari (Neeraj)

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 Tan

ASKER

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)

Please upload a sample workbook with data in columns M and N to find out why it doesn't work.

aikimark

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 Tan

ASKER

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..

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial