Color text macro
 

Before
Projects |Dates
100101 |17-Jul-2005
100101 |19-Jul-2005
100102 |01-Apr-2005
100102 |30-Jul-2005
100102 |31-Jul-2005
100103 |29-Jul-2005
100104 |17-Jul-2005
100105 |17-Jul-2005

After
Projects |Dates
100101 |17-Jul-2005
100101 |19-Jul-2005
100102 |01-Apr-2005
100102 |30-Jul-2005
100102 |31-Jul-2005
100103 |29-Jul-2005
100104 |17-Jul-2005
100105 |17-Jul-2005

I need help to create a macro to color code the project numbers. In
this example 100101 has two projects...I would like to color the 1st
two letters of both projects red to know that there are 2 projects
with this project number when i do a filter. A second example; there
are three project 100102, I would like the first three digits red to
know that there are three projects with the same project number as this
one. etc...

Any help is appreciated.
Thx.


--
mister z
------------------------------------------------------------------------
mister z's Profile: http://www.excelforum.com/member.php...o&userid=28099
View this thread: http://www.excelforum.com/showthread...hreadid=476359

Color text macro
 

If these project numbers are stored as numbers, then you can't color
individual digits. You need to store them as strings. (the code does that
if they are not already).

Sub abc()
Dim lastrow As Long, startrow As Long
Dim rng As Range, cell As Range
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
startrow = 1
For i = 2 To lastrow + 1
If Cells(i, 1) < Cells(i - 1, 1) Then
Set rng = Range(Cells(startrow, 1), Cells(i - 1, 1))
For Each cell In rng
cell.Value = "'" & cell.Value
cell.Characters(1, rng.Count).Font.ColorIndex = 3
startrow = i
Next
End If
Next
End Sub

worked for me.




--
Regards,
Tom Ogilvy

"mister z" wrote in
message ...

Before
Projects |Dates
100101 |17-Jul-2005
100101 |19-Jul-2005
100102 |01-Apr-2005
100102 |30-Jul-2005
100102 |31-Jul-2005
100103 |29-Jul-2005
100104 |17-Jul-2005
100105 |17-Jul-2005

After
Projects |Dates
100101 |17-Jul-2005
100101 |19-Jul-2005
100102 |01-Apr-2005
100102 |30-Jul-2005
100102 |31-Jul-2005
100103 |29-Jul-2005
100104 |17-Jul-2005
100105 |17-Jul-2005

I need help to create a macro to color code the project numbers. In
this example 100101 has two projects...I would like to color the 1st
two letters of both projects red to know that there are 2 projects
with this project number when i do a filter. A second example; there
are three project 100102, I would like the first three digits red to
know that there are three projects with the same project number as this
one. etc...

Any help is appreciated.
Thx.


--
mister z
------------------------------------------------------------------------
mister z's Profile:
http://www.excelforum.com/member.php...o&userid=28099
View this thread: http://www.excelforum.com/showthread...hreadid=476359

Color text macro
 

Worked like a charm. True geniusness. Thanks!


--
mister z
------------------------------------------------------------------------
mister z's Profile: http://www.excelforum.com/member.php...o&userid=28099
View this thread: http://www.excelforum.com/showthread...hreadid=476359