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

Experts Exchange Solution brought to you by

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

Author Commented:
Thank you both! Val function worked perfectly.
0
Excel & VBA ExpertCommented:
You're welcome Pearlyn! Glad we could help.
0
###### 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.