Text color question?
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
-----------------------------------------
Regards,
Jim Cone
San Francisco, CA
"Michael168 " wrote in message ...
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 negative
figures in another color. I try the macro recorder but it won't work.
Regards,
Michael.
|