Numeric+Text Column

[ainz002]

Hi there! I would like to ask if someone can help me witht this excel case:

A1	B1	C1
1.4um Thick	1.2um Thick	Test 1

<tbody>
</tbody>

I want t compare the numbers in A1 and B1 so that the text in C1 will be highlighted.
Example: If A1>B1 (1.4>1.2), then 'Test 1' in C1 will have red text.

Is there a way to do this without having to add another column to separate the number from the text?

Thanks alot in advance!

 

[asif_raza]

hi aniz! you can use left formula to extract numeric numbers only. as if(left(A1,3)>left(B1,3) then Range(C1).Font.Bold

 

[konew1]

You should use conditional formatting to create the red font.
Use this formula in the conditional formatting
(--LEFT(A2,FIND("um",A2)-1))>(--LEFT(B2,FIND("um",B2)-1))
This will work even if the numbers are not all the same number of digits, but it does require all numbers to be followed by "um"

 

[L. Howard]

Try conditional formatting, select C1 then Home tab > Conditional formatting > Highlight cell rules > More rules > New Formatting rules > Use formula to determine which cell to format > edit the rule description > =--LEFT(A1,3)>--LEFT(B1,3) > Format > Color > Red OK out.

Regards,
Howard

 

[Dryver14]

select cell A3 then formula in conditional formatting

=LEFT($A$1,3)>LEFT($B$1,3)

 

[ainz002]

Thanks everyone for your quick response. It saved me many hours of trying to browse the net for the solution. haha! Btw, I used L. Howard's suggestion.

 

[ainz002]

Try conditional formatting, select C1 then Home tab > Conditional formatting > Highlight cell rules > More rules > New Formatting rules > Use formula to determine which cell to format > edit the rule description > =--LEFT(A1,3)>--LEFT(B1,3) > Format > Color > Red OK out.

Regards,
Howard

Thanks for the response and instruction L. Howard. It's not really complicated as I thought it would be.

 

[L. Howard]

You are welcome.

Thanks for the feed back.

Regards,
Howard

 

[ainz002]

Hi! I have a follow-question. If there is at least one 'red text' in Column C, I would like to make overall assessment in Column C like ex. C30: "Experiment Failed".
This is another conditional formatting but i dont know what is the formula. Thanks once again!

 

[L. Howard]

In the vb editor look at the top left of the page between View and Format. Click on INSERT then Module. When you get the module paste this in it.
On the worksheet in a cell enter , "=countcolor($C$1:$C$25,255)" (no quotes) and adjust the range to cover your red font anticipated cells.
To check on your experiment press F9. The code will fire with each calculation on the sheet which may be a bother but will only produce the message if you have red font in the range in the formula.

Option Explicit
Function countcolor(rng As Range, colorindex As Integer)
 Dim count As Integer
 count = 0
 Dim rw As Range
 For Each rw In rng
    If rw.Font.Color = colorindex Then
     count = count + 1
   End If
 Next rw
 countcolor = count
 If countcolor > 0 Then
 MsgBox "Booger on your Experiment"
 End If
 End Function

'On the sheet use =countcolor($C$1:$C$25,255)

Regards,
Howard