Compare comma separated numbers

[sindrefm]

I got two columns, Low and High, with values like this:

LowHigh.jpg

I have a code that is to fill the low cell with red color if Low.Value > High.Value.

If .Cells(i, ColLow).Value > .Cells(i, ColHigh).Value Then
.Cells(i, ColLow).Interior.Color = vbRed
End If

The problem is with the comma separated values.
If I have ex.
Low.Value is 3,2 and High.Value is 16.
It seems like the code is reading Low.Value 32 and High.Value 16, and executing wrong.

Can anyone help on this?
Thanks.

[Aflatoon]

It would appear that your cells contain text, not true numbers. Try using:

If CDbl(.Cells(i, ColLow).Value) > CDbl(.Cells(i, ColHigh).Value) Then

assuming your regional settings use commas as decimal point.

[sindrefm]

I get an error message "Type mismatch" when using CBbl. And yes, cells contain text.
do you know any other ways?

[Aflatoon]

Do your regional settings use a comma as a decimal point?

[sindrefm]

Where can I find those settings?

When I hold the mouse pointer over the cell I get this message:
"the number in this cell is formatted as text or preceded by an apostrophe"

And Format setting --> Number Category --> General format

[sindrefm]

Regional settings is European --> Norwegian

[Aflatoon]

If you were to enter 12 and a half in a cell, would you enter 12,5 or 12.5?

[sindrefm]

I would enter 12,5

[Aflatoon]

That's odd then since CDbl should use your regional settings.
if you change the code to this, does it help:

If CDbl(Trim(.Cells(i, ColLow).Value)) > CDbl(Trim(.Cells(i, ColHigh).Value)) Then

[sindrefm]

No, I get the same error message... "Type mismatch"

[sindrefm]

Error.jpg

[Kenneth Hobs]

To make the values a number when single quotes were used, use a method like:

Range("A2:A5").Value = Range("A2:A5").Value

[Aflatoon]

That's odd - it works fine for me if I change my settings to Norwegian.

Just for debugging, can you try using:

If Not IsNumeric(.Cells(i, ColLow).Value) Then    MsgBox .Cells(i, ColLow).Value & " is not a number"
ElseIf Not IsNumeric(.Cells(i, ColHigh).Value) Then
    MsgBox .Cells(i, ColHigh).Value & " is not a number"
ElseIf CDbl(.Cells(i, ColLow).Value) > CDbl(.Cells(i, ColHigh).Value) Then
    .Cells(i, ColLow).Interior.Color = vbRed
End If

and tell me what the message says?

[SamT]

Where can I find those settings?

When I hold the mouse pointer over the cell I get this message:
"the number in this cell is formatted as text or preceded by an apostrophe"

And Format setting --> Number Category --> General format

Format Cells >> Number >> Category >> Special >> Locale (Location) >> Norwegian (Bokmal or Nynorsk)
Then go to
Number >> Category >> Number and adjust appearance to your desire.

That should correct this workbook.

Then go to the computers Control Panel >> Regional and Language Options

Then go to Excel Menu >> Tools >> Options >> International.

[sindrefm]

SamT, I tried the debugging and I got the message box " is not a number".

[Aflatoon]

OK - that makes sense; you're processing blank cells. You can add a check like this:

If IsNumeric(.Cells(i, ColLow).Value) And IsNumeric(.Cells(i, ColHigh).Value) Then    If CDbl(.Cells(i, ColLow).Value) > CDbl(.Cells(i, ColHigh).Value) Then .Cells(i, ColLow).Interior.Color = vbRed
End If

[sindrefm]

Awesome!
Thank you very much for the help