VBA Loop Formatting Text Question
 

I am having a diffifult time writing this bit of code and I am hoping someone
can help me out.

I have a large data set with varying row numbers each week. I have a column
J that I want to loop through and Bold and Colorthe cells with a "Y" value.
Here is my current code:

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
If XL.ActiveCell.Value = "Y" Then
XL.ActiveCell.Interior.ColorIndex = 6
XL.ActiveCell.Font.Bold = True
End If
Next

It works fine but takes a while because it selects the entire column (all
the way to the end). I don't need to go that far down, just to the last row
of data.

Can anyone show me how to select the entire column down to the last row that
contains data.

Thanks in advance.

VBA Loop Formatting Text Question
 

Actually, ActiveCell never changes in your loop, so I doubt it actually does
what you want.

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
if isempty(curCell) then exit for
If CurCell.Value = "Y" Then
CurCell.Interior.ColorIndex = 6
CurCell.Font.Bold = True
End If
Next

or

Dim rng as Range, curcell as Range
Dim xl as application
set xl = Application
With xl.Activesheet
set rng = .Range(.Cells(1,"J"),.Cells(rows.count,"J").End(xl up))
End with
for each curcell in rng
if curcell.Value = "Y" then
curcell.Interior.colorIndex = 6
curcell.font.Bold = True
end if
next curcell

--
Regards,
Tom Ogilvy


"Richard" wrote:

I am having a diffifult time writing this bit of code and I am hoping someone
can help me out.

I have a large data set with varying row numbers each week. I have a column
J that I want to loop through and Bold and Colorthe cells with a "Y" value.
Here is my current code:

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
If XL.ActiveCell.Value = "Y" Then
XL.ActiveCell.Interior.ColorIndex = 6
XL.ActiveCell.Font.Bold = True
End If
Next

It works fine but takes a while because it selects the entire column (all
the way to the end). I don't need to go that far down, just to the last row
of data.

Can anyone show me how to select the entire column down to the last row that
contains data.

Thanks in advance.

VBA Loop Formatting Text Question
 

My bad. I was modifying the code quite a bit so I had to undo, all instances
of ActiveCell should read CurrCell.

"Tom Ogilvy" wrote:

Actually, ActiveCell never changes in your loop, so I doubt it actually does
what you want.

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
if isempty(curCell) then exit for
If CurCell.Value = "Y" Then
CurCell.Interior.ColorIndex = 6
CurCell.Font.Bold = True
End If
Next

or

Dim rng as Range, curcell as Range
Dim xl as application
set xl = Application
With xl.Activesheet
set rng = .Range(.Cells(1,"J"),.Cells(rows.count,"J").End(xl up))
End with
for each curcell in rng
if curcell.Value = "Y" then
curcell.Interior.colorIndex = 6
curcell.font.Bold = True
end if
next curcell

--
Regards,
Tom Ogilvy


"Richard" wrote:

I am having a diffifult time writing this bit of code and I am hoping someone
can help me out.

I have a large data set with varying row numbers each week. I have a column
J that I want to loop through and Bold and Colorthe cells with a "Y" value.
Here is my current code:

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
If XL.ActiveCell.Value = "Y" Then
XL.ActiveCell.Interior.ColorIndex = 6
XL.ActiveCell.Font.Bold = True
End If
Next

It works fine but takes a while because it selects the entire column (all
the way to the end). I don't need to go that far down, just to the last row
of data.

Can anyone show me how to select the entire column down to the last row that
contains data.

Thanks in advance.

VBA Loop Formatting Text Question
 

Thanks Tom. Works like a charm

"Tom Ogilvy" wrote:

Actually, ActiveCell never changes in your loop, so I doubt it actually does
what you want.

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
if isempty(curCell) then exit for
If CurCell.Value = "Y" Then
CurCell.Interior.ColorIndex = 6
CurCell.Font.Bold = True
End If
Next

or

Dim rng as Range, curcell as Range
Dim xl as application
set xl = Application
With xl.Activesheet
set rng = .Range(.Cells(1,"J"),.Cells(rows.count,"J").End(xl up))
End with
for each curcell in rng
if curcell.Value = "Y" then
curcell.Interior.colorIndex = 6
curcell.font.Bold = True
end if
next curcell

--
Regards,
Tom Ogilvy


"Richard" wrote:

I am having a diffifult time writing this bit of code and I am hoping someone
can help me out.

I have a large data set with varying row numbers each week. I have a column
J that I want to loop through and Bold and Colorthe cells with a "Y" value.
Here is my current code:

XL.Columns("J").Select
Dim CurCell As Object
For Each CurCell In XL.Selection
If XL.ActiveCell.Value = "Y" Then
XL.ActiveCell.Interior.ColorIndex = 6
XL.ActiveCell.Font.Bold = True
End If
Next

It works fine but takes a while because it selects the entire column (all
the way to the end). I don't need to go that far down, just to the last row
of data.

Can anyone show me how to select the entire column down to the last row that
contains data.

Thanks in advance.