# 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
``````
###### Who is Participating?

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.

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
``````
Author 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
``````
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?
Excel & VBA ExpertCommented:
Please upload a sample workbook with data in columns M and N to find out why it doesn't work.
Commented:
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
``````
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)
``````
Author Commented:

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..
Commented:
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.
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
``````
Here is the output based on your sample data. The cells are highlighted based on condition Mmax < Mmin.

Experts Exchange Solution brought to you by