Thread: 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.

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(xlup))
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.

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(xlup))
> 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.

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(xlup))
> 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.