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.