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

Hi! Dana DeLouis,

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

Thanks for the info.

Regards,
Michael.

Dana DeLouis wrote:
*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


"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/
*


---
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/

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


"Michael168 " wrote in message
...
Hi! Dana DeLouis,

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

Thanks for the info.

Regards,
Michael.

Dana DeLouis wrote:
*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


"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/
*


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