Text color question?

M

Michael168

Hi,VBA pro,

I have a column in a worksheet which hold text string like below;

1,-5,6,-11........
-1,2,-3,............

How can I made the the postive figures in one color and the negativ
figures in another color. I try the macro recorder but it won't work.

Regards,
Michael
 
J

Jim Cone

Michael,

The following code colors all minus numbers red.
It assumes...
All numbers are separated by a comma
All cells are formatted as text
You have selected the range containing the data

It looks for a "dash" then looks for the first comma occurring after the dash.
It then colors the text red from the dash to the character just before the comma.
If no comma after a dash is found, it colors everything after the dash red.
Only limited testing has been done.
Please let us know if it works for you.

'---------------------------------------
Sub ShowNegativesInRed()
Dim objCell As Range
Dim strText As String
Dim lngChar As Long
Dim lngNextChar As Long

For Each objCell In Selection
strText = objCell.Text
If Len(strText) Then
For lngChar = 1 To (Len(strText) - 1)
If Asc(Mid$(strText, lngChar, 1)) = 45 Then
lngNextChar = InStr(lngChar + 1, strText, ",", vbTextCompare)
If lngNextChar = 0 Then lngNextChar = Len(strText) + 1
objCell.Characters(lngChar, lngNextChar - lngChar).Font.ColorIndex = 3
End If
Next 'lngChar
End If
Next 'ojbCell
Set objCell = Nothing
End Sub
 
D

Dana DeLouis

If you would like to try something a little different. If you can set a vba
library reference to "Regular Expressions 5.5" as listed below, this should
turn positive numbers to Green, and negative numbers to Red. I have it set
to work on a "Selection", but you can adjust it easily.

Sub RedGreen()
'// Needs: Microsoft VBScript Regular Expressions 5.5
'// Dana DeLouis

Dim BigRng As Range
Dim cell As Range
Dim Matches
Dim Match

On Error Resume Next
Set BigRng = Selection.SpecialCells(xlConstants, xlTextValues)
If BigRng Is Nothing Then Exit Sub

With New RegExp
.IgnoreCase = True
.Global = True
.Pattern = "(-*\d+)"

For Each cell In BigRng.Cells
If .Test(cell) Then
Set Matches = .Execute(cell)
For Each Match In Matches
With cell.Characters(Start:=Match.FirstIndex + 1,
Length:=Match.Length).Font
.ColorIndex = IIf(Match.Value >= 0, 10, 3)
End With
Next Match
End If
Next cell
End With
Debug.Print
End Sub


HTH
Dana DeLouis
 
M

Michael168

Hi! Jim Cone,
Yes,the code works but how to modify it so that it will use column 2 a
range for the objcell. Is it possible to add in a sub or whatsoever s
that the positive values will become blue in color?

Thanks for helping.

Regards,
Michael.
 
M

Michael168

Hi! Dana DeLouis,

I like to try your method but how to set the vba library. Please
guide.

Thanks for the info.

Regards,
Michael.
 
J

Jim Cone

Michael,

My code works on whatever cells are selected, so if you want column 2 then select it.
However,there is no reason to revise my code, just use the Dana DeLouis code.

In case Dana is still sleeping...

Replace "10" in the following line with 5.
.ColorIndex = IIf(Match.Value >= 0, 10, 3)
(10 is green, 5 is blue)

Also, in the Visual Basic Editor, go to Tools | References and put a check by
"Microsoft VBScript Regular Expressions 5.5" in the list that appears.

Regards,
Jim Cone
San Francisco, CA
 
D

Dana DeLouis

Hi. In the vba editor, select Tools | References..
and select, if you have it, the following:
"Microsoft VBScript Regular Expressions 5.5"

If needed, record a macro as you select your specific colors to get your
particular "ColorIndex."

The idea behind this approach is that "Matches" returns the information you
need for Characters( ).Font (ie Start & Length)

HTH.
Dana DeLouis
 
Top