About Us

mcphc

That got it! Cheers Tom

"Tom Ogilvy" wrote:

Sub Tester1()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, cell As Range
Set rng = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
Set rng1 = rng.Rows(1).Cells
Set rng2 = rng.Columns(1).Cells
For j = rng1(1).Column To rng1(rng1.Count).Column - 5
For i = rng2(1).Row To rng2(rng2.Count).Row
Set cell = Cells(i, j)
cell.Interior.ColorIndex = 3
Next i, j

End Sub

--
Regards,
Tom Ogilvy

"mcphc" wrote:

Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks

"Jason Lepack" wrote:

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

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
Reference a number from a different column in a formula	rhhince[_2_]	Excel Worksheet Functions	2	November 9th 08 11:15 PM
Extract Column Letter from Cell Reference in another Cell	JKBEXCEL	Excel Discussion (Misc queries)	2	December 29th 06 04:27 PM
Reference a Column by Number	John Michl	Excel Programming	4	June 1st 06 10:01 PM
Selecting a range using a column number reference	PCLIVE	Excel Programming	7	October 14th 05 04:59 PM
reference to column (not known) and certain row number	gaba	Excel Programming	3	November 15th 04 01:34 PM

Menu

About Us