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

--
Message posted from http://www.ExcelForum.com

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.

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.

Jim Cone wrote:
*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 afte
the dash.
It then colors the text red from the dash to the character jus
before the comma.
If no comma after a dash is found, it colors everything after th
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 i
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 th
negative
figures in another color. I try the macro recorder but it won'
work.
Regards,
Michael.

--
Message posted from http://www.ExcelForum.com

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



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

Thanks for helping.

Regards,
Michael.

Jim Cone wrote:
*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. *


---
Message posted from http://www.ExcelForum.com/