Instruct to start in Row 5
 

Hello from Steved

Please how do I start the below form Row 5, I Thanyou in advance.

Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D:D"))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub

Instruct to start in Row 5
 

This should do it for you!


Code:
--------------------
Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D5:D" & Range("D" & rows.count).end(xlup).row))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub
--------------------


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34744

Instruct to start in Row 5
 

Steved,

Try code like the following

Dim RR As Range
Dim R As Range
Dim WS As Worksheet
Set WS = ActiveSheet
With WS
Set RR = Application.Intersect(.UsedRange, _
.Range(.Cells(5, "D"), .Cells(.Rows.Count, "D")))
End With

For Each R In RR.Cells
' do something
Next R


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 27 Nov 2008 12:05:01 -0800, Steved
wrote:

Hello from Steved

Please how do I start the below form Row 5, I Thanyou in advance.

Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D:D"))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub

Instruct to start in Row 5
 

Depending on the data content, that code may miss some lines at the
bottom of the data. For example, if the data goes down to row 100 but
the last non-blank element in column D is at 80, rows 81-100 won't be
processed. This may or may not be relevant to the problem at hand.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 27 Nov 2008 20:16:01 +0000, The Code Cage Team
wrote:


This should do it for you!


Code:
--------------------
Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D5:D" & Range("D" & rows.count).end(xlup).row))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub
--------------------

Instruct to start in Row 5
 

Hello Chip from Steved

Chip I run the below and it is not underling the 4th Digit also it is not
changing the 4th digit to bold and Red. Yes I inserted it into a module I
also did a view code insert. To my thinking I've forgotten to do something.
Thankyou.


Sub Underline4thDigit()
Dim RR As Range
Dim R As Range
Dim WS As Worksheet
Set WS = ActiveSheet
With WS
Set RR = Application.Intersect(.UsedRange, _
.Range(.Cells(5, "D"), .Cells(.Rows.Count, "D")))
End With

For Each R In RR.Cells
With R.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next R
End Sub





"Chip Pearson" wrote:

Depending on the data content, that code may miss some lines at the
bottom of the data. For example, if the data goes down to row 100 but
the last non-blank element in column D is at 80, rows 81-100 won't be
processed. This may or may not be relevant to the problem at hand.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 27 Nov 2008 20:16:01 +0000, The Code Cage Team
wrote:


This should do it for you!


Code:
--------------------
Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D5:D" & Range("D" & rows.count).end(xlup).row))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub
--------------------

Instruct to start in Row 5
 

Hello from Steved

my Fault it does work if it is 2442 but if it is 0042 it cut's of the "00"
how can I avoid this please.


"Steved" wrote:

Hello Chip from Steved

Chip I run the below and it is not underling the 4th Digit also it is not
changing the 4th digit to bold and Red. Yes I inserted it into a module I
also did a view code insert. To my thinking I've forgotten to do something.
Thankyou.


Sub Underline4thDigit()
Dim RR As Range
Dim R As Range
Dim WS As Worksheet
Set WS = ActiveSheet
With WS
Set RR = Application.Intersect(.UsedRange, _
.Range(.Cells(5, "D"), .Cells(.Rows.Count, "D")))
End With

For Each R In RR.Cells
With R.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next R
End Sub





"Chip Pearson" wrote:

Depending on the data content, that code may miss some lines at the
bottom of the data. For example, if the data goes down to row 100 but
the last non-blank element in column D is at 80, rows 81-100 won't be
processed. This may or may not be relevant to the problem at hand.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 27 Nov 2008 20:16:01 +0000, The Code Cage Team
wrote:


This should do it for you!


Code:
--------------------
Sub Underline4thDigit()
Dim myS As String
Dim myD As Range

For Each myD In Intersect(ActiveSheet.UsedRange, Range("D5:D" & Range("D" & rows.count).end(xlup).row))
myS = myD.Value
myD.NumberFormat = "@"
myD.Value = myS
With myD.Characters(Start:=4, Length:=1).Font
.FontStyle = "Bold"
.Color = -16776961
.Underline = xlUnderlineStyleSingle
End With
Next myD
End Sub
--------------------

Instruct to start in Row 5
 

Chip, naturally you are correct, quite an oversite from me,
thanks for setting me straight.


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34744