In Over My Head
 

Hello again Gods of Excel. Here's what I'm struggling with this week: I need
to use the value from sheet "SET UP" cell "C2" (which is a whole number,
let's say 50) on all my other sheets (Jan - Dec) in as a counter. I'd like
to accomplish two things on all the other sheets: 1) starting w/ row 6 in
column A, list 1 through whatever the number for C2 is on "SET UP" sheet; 2)
format cells B6:K56 (row 6 + 50 in this example) with white background, grey
shading etc.

So if I entered 50 on the SET UP sheet, all the other pages would have 50
lines formatted with 1-50 listed in column A. What if I changed that number
to 30? Wouldn't the formatting remain for the rows labelled 31-50?

Thanks in advance for any help.

In Over My Head
 

One way assuming Setup is your first sheet.
If you do NOT want to clear the old numbers delete the .columns(1) line

Sub numbersinshts()
mynum = 8 'cells("c2")
For i = 2 To Sheets.Count
With Sheets(i)
.Columns(1).ClearContents
With .Cells(6, 1).Resize(mynum)
.Formula = "=Row(a1)"
.Value = .Value
End With
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JSnow" wrote in message
...
Hello again Gods of Excel. Here's what I'm struggling with this week: I
need
to use the value from sheet "SET UP" cell "C2" (which is a whole number,
let's say 50) on all my other sheets (Jan - Dec) in as a counter. I'd
like
to accomplish two things on all the other sheets: 1) starting w/ row 6 in
column A, list 1 through whatever the number for C2 is on "SET UP" sheet;
2)
format cells B6:K56 (row 6 + 50 in this example) with white background,
grey
shading etc.

So if I entered 50 on the SET UP sheet, all the other pages would have 50
lines formatted with 1-50 listed in column A. What if I changed that
number
to 30? Wouldn't the formatting remain for the rows labelled 31-50?

Thanks in advance for any help.

In Over My Head
 

Hello again Gods of Excel. Here's what I'm struggling with this week:
I need to use the value from sheet "SET UP" cell "C2" (which is a
whole number, let's say 50) on all my other sheets (Jan - Dec) in as a
counter. I'd like to accomplish two things on all the other sheets:
1) starting w/ row 6 in column A, list 1 through whatever the number
for C2 is on "SET UP" sheet; 2) format cells B6:K56 (row 6 + 50 in
this example) with white background, grey shading etc.

So if I entered 50 on the SET UP sheet, all the other pages would have
50 lines formatted with 1-50 listed in column A. What if I changed
that number to 30? Wouldn't the formatting remain for the rows
labelled 31-50?

I have Excel 2003. Maybe the following will help.

First, tackle the background/shading using conditional formatting. To do
this, start with
Insert Name Define
and
- for "Names in workbook" put "RowCount"
- for "Refers to" click on C2 on "SET UP" sheet

Then (a) select all the month-sheet tabs and (b) select the range of cells
to be formatted, and (c) use
Format Conditional Formatting Formula Is
with use the formula
=AND(ROW()=6,ROW()<6+RowCount)
and click the format button to choose the desired background/shading.


Next comes the list starting at A6 of the "SET UP" sheet. In A6 put
=IF(ROW()-5RowCount,"",
INDIRECT(TEXT(DATE(2008,COLUMN(),1),"Mmm")&"!$A"&R OW()))
Extend this to the right to L6. Select A6:L6 and extend down for the
maximum number of rows of data.