About Us

Richard

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Numeric date to text..formatting question	JHB	Excel Discussion (Misc queries)	2	October 4th 08 07:46 PM
For Next Loop Question	GregR	Excel Programming	8	May 27th 06 03:56 PM
loop question trying to bring excel into autocad text not starting in correct place	[email protected]	Excel Programming	0	February 10th 06 12:59 PM
loop question in VBA	h2o[_3_]	Excel Programming	1	February 1st 06 08:33 PM
scientific notation to text formatting question	dtdd	Excel Programming	2	January 13th 04 07:08 PM

Menu

About Us