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!

 

[ainz002]

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

Hi Howard! Thanks for being of assistance again. I have done exactly as instructed but the cell on the sheet will give '0' value regardless if I have or do not have red font items on the column. I'm still trying to figure out where did I go wrong.

 

[ainz002]

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

Hi Howard! Thanks for being of assistance again. I have done everything as instructed. But on the sheet cell, it gives me '0' value rgardless if the column has or doesn't have red font text. I'm still trying to figure out where did I go wrong..

 

[L. Howard]

I may have missled you with the F9 caper. I am using a lap top HP Pavilion g6. The F keys don't seem to coperate as they do on my desk top, so I thought it would be just fine with a calculate key stroke even though F9 doesn't work for me on this lap top.

Try this, on the cell with the formula =countcolor($C$1:$C$25,255) double click then Enter.

And the cell font color is NOT due to conditional formatting.

Howard

 

[ainz002]

Well, the cell font color is due to the conditional formatting that I did early part of this thread. If that's the case, is there a way I can still get my desired result? Thanks!

 

[L. Howard]

This may be worth a try. In C1 and pull down. (I went to C10 in my test)

=IF(--LEFT(A1,3)>--LEFT(B1,3),"TEST FAILED","Test  OK")

In the sheet module copy and paste this change event macro.

Option Explicit
Private Declare Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:C10")) Is Nothing _
  Or Target.Cells.count > 1 Then Exit Sub
Dim i As Range
For Each i In Range("C1:C10")
  If i.Value = "TEST FAILED" Then
    sndPlaySound32 "C:\Windows\Media\Chimes.wav", 0&
    MsgBox "TEST FAILED in cell " & i.Address
  End If
Next
End Sub

Any change in the range A1:C10 will fire the code, if there is a "Test Failed" in column C you get a msgbox alert of the failed test AND the cell address.

Or a visual scan of column C will show TEST FAILED.

And we can ditch the "tweedle tweedle dee" sound if you want

Regards,
Howard

 

[ainz002]

Hi! Well I've tried to work around with the suggestion of Howard but still not successful. Here is what I did instead (kind of a long route though). I made another column (A) and did another conditional formatting that will display "Passed" or "Failed". Now in another cell, I want it to display that if the new column contains "Failed" the cell will display "Experiment Failed".

I have used this one:
=IF(ISERR(FIND("Failed",A12:A125)),"Experiment Failed","Experiment Passed")

But the problem is even if the column has no "Failed" instead only "Passed" and some empty rows, it will still display "Experiment Failed". I think this needs little tweaking on what I have started. Can someone help? Thanks!

 

[L. Howard]

If you think you would be happy with my last code suggestion send me a copy of your workbook, a plain jane version will do I will see if I can make it work for you.

Remove the spaces and I am at:

lhkittle @ comcast. net

Howard

 

[ainz002]

If you think you would be happy with my last code suggestion send me a copy of your workbook, a plain jane version will do I will see if I can make it work for you.

Remove the spaces and I am at:

lhkittle @ comcast. net

Howard

Sorry for the late post. Big Thanks to L. Howard for helping me on this. Already have what I needed. This is a really helpful forum. Thanks once again!