I have a large spreadsheet that I'd like to use conditional formatting on the
rows. I've seen examples on how to apply it to every other row, but in my
case, I have groups of data that remains together in blocks of 8 rows each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need to
insert another "block" into the spreadsheet and find myself re-formatting the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

Essentially you need to get the same no. (SAY IN COL D) 1 for the first 8
nos, then 2 for next 8, and finally 3 for next 8 and then repeat the pattern.
Then format conditionally like (after selecting the whole range)
Condition 1 FORMULA IS =$D1=1 WHITE
Condition 2 FORMULA IS =$D1=2 GREY
Condition 1 FORMULA IS =$D1=3 BLUE

I have done it in the following way, you can combine the formulae to get
into one Col.

Enter in A6
=MOD(ROW()+2,24)

Enter in B6
=MOD(A6,8)

Enter in C6
1
and in C7
=IF(B7=0,C6+1,C6)


Enter in D6
=MOD(C6-1,3)+1

and copy the formulae down (careful for C as you have to copy down from C7)

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting on the
rows. I've seen examples on how to apply it to every other row, but in my
case, I have groups of data that remains together in blocks of 8 rows each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need to
insert another "block" into the spreadsheet and find myself re-formatting the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

Add one to the formula in Col D
=MOD(C6-1,3)+1
otherwise you will get cycles of 0,1,2 instead of 1,2,3 as mentioned by me.

Do mark the question as answered if this works for you

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting on the
rows. I've seen examples on how to apply it to every other row, but in my
case, I have groups of data that remains together in blocks of 8 rows each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need to
insert another "block" into the spreadsheet and find myself re-formatting the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

Here is the combined formula which you can enter

1 in A1

and this in A2

=MOD(IF(MOD(MOD(ROW()+2,24),8)=0,C6+1,C6)-1,3)+1

and then use =$A1=1 etc.

Do let me know if you can think of a simpler formula.
--
Always provide your feedback...


"Sheeloo" wrote:

Add one to the formula in Col D
=MOD(C6-1,3)+1
otherwise you will get cycles of 0,1,2 instead of 1,2,3 as mentioned by me.

Do mark the question as answered if this works for you

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting on the
rows. I've seen examples on how to apply it to every other row, but in my
case, I have groups of data that remains together in blocks of 8 rows each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need to
insert another "block" into the spreadsheet and find myself re-formatting the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

If white is the default fill color you only need to test for gray and blue.

Select the range of cells in question. Assume it's A6:An
Goto the menu FormatConditional Formatting
Condition 1 (gray)
Select the Formula Is option
Enter this formula in the box on the right:

=AND(MOD(ROWS(A$6:A6),24)=9,MOD(ROWS(A$6:A6),24)< =16)

Click the Format button
Select a GRAY fill color
OK

Click the Add button

Condition 2 (blue)
Select the Formula Is option
Enter this formula in the box on the right:

=OR(MOD(ROWS(A$6:A6),24)=0,AND(MOD(ROWS(A$6:A6),24 )=17,MOD(ROWS(A$6:A6),24)<=23))

Click the Format button
Select a BLUE fill color
OK out

--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Essentially you need to get the same no. (SAY IN COL D) 1 for the first 8
nos, then 2 for next 8, and finally 3 for next 8 and then repeat the
pattern.
Then format conditionally like (after selecting the whole range)
Condition 1 FORMULA IS =$D1=1 WHITE
Condition 2 FORMULA IS =$D1=2 GREY
Condition 1 FORMULA IS =$D1=3 BLUE

I have done it in the following way, you can combine the formulae to get
into one Col.

Enter in A6
=MOD(ROW()+2,24)

Enter in B6
=MOD(A6,8)

Enter in C6
1
and in C7
=IF(B7=0,C6+1,C6)


Enter in D6
=MOD(C6-1,3)+1

and copy the formulae down (careful for C as you have to copy down from
C7)

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting on
the
rows. I've seen examples on how to apply it to every other row, but in
my
case, I have groups of data that remains together in blocks of 8 rows
each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need
to
insert another "block" into the spreadsheet and find myself re-formatting
the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first
block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

This worked fine and it formatted my spreadsheet as it exists. The issue I
experienced was that when I went to add a new block of rows (8 rows) the row
color was formatted the same as at the insertion point. I would like the
spreadsheet rows to automatically reformat and update when new rows are
inserted. Is this possible?
--
Cathy


"T. Valko" wrote:

If white is the default fill color you only need to test for gray and blue.

Select the range of cells in question. Assume it's A6:An
Goto the menu FormatConditional Formatting
Condition 1 (gray)
Select the Formula Is option
Enter this formula in the box on the right:

=AND(MOD(ROWS(A$6:A6),24)=9,MOD(ROWS(A$6:A6),24)< =16)

Click the Format button
Select a GRAY fill color
OK

Click the Add button

Condition 2 (blue)
Select the Formula Is option
Enter this formula in the box on the right:

=OR(MOD(ROWS(A$6:A6),24)=0,AND(MOD(ROWS(A$6:A6),24 )=17,MOD(ROWS(A$6:A6),24)<=23))

Click the Format button
Select a BLUE fill color
OK out

--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
Essentially you need to get the same no. (SAY IN COL D) 1 for the first 8
nos, then 2 for next 8, and finally 3 for next 8 and then repeat the
pattern.
Then format conditionally like (after selecting the whole range)
Condition 1 FORMULA IS =$D1=1 WHITE
Condition 2 FORMULA IS =$D1=2 GREY
Condition 1 FORMULA IS =$D1=3 BLUE

I have done it in the following way, you can combine the formulae to get
into one Col.

Enter in A6
=MOD(ROW()+2,24)

Enter in B6
=MOD(A6,8)

Enter in C6
1
and in C7
=IF(B7=0,C6+1,C6)


Enter in D6
=MOD(C6-1,3)+1

and copy the formulae down (careful for C as you have to copy down from
C7)

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting on
the
rows. I've seen examples on how to apply it to every other row, but in
my
case, I have groups of data that remains together in blocks of 8 rows
each
(this is consistent). The other issue, is that I want the formatting to
start on row 6 (the consistent blocking starts on row 7). I often need
to
insert another "block" into the spreadsheet and find myself re-formatting
the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first
block
of 8 rows white, next 8 grey, next 8 blue and then repeat the conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

I think you're going to need a macro do that. Try posting in the programming
forum and make sure you explain in great detail what you're trying to do.

--
Biff
Microsoft Excel MVP


"Cathy" wrote in message
...
This worked fine and it formatted my spreadsheet as it exists. The issue
I
experienced was that when I went to add a new block of rows (8 rows) the
row
color was formatted the same as at the insertion point. I would like the
spreadsheet rows to automatically reformat and update when new rows are
inserted. Is this possible?
--
Cathy


"T. Valko" wrote:

If white is the default fill color you only need to test for gray and
blue.

Select the range of cells in question. Assume it's A6:An
Goto the menu FormatConditional Formatting
Condition 1 (gray)
Select the Formula Is option
Enter this formula in the box on the right:

=AND(MOD(ROWS(A$6:A6),24)=9,MOD(ROWS(A$6:A6),24)< =16)

Click the Format button
Select a GRAY fill color
OK

Click the Add button

Condition 2 (blue)
Select the Formula Is option
Enter this formula in the box on the right:

=OR(MOD(ROWS(A$6:A6),24)=0,AND(MOD(ROWS(A$6:A6),24 )=17,MOD(ROWS(A$6:A6),24)<=23))

Click the Format button
Select a BLUE fill color
OK out

--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote
in
message ...
Essentially you need to get the same no. (SAY IN COL D) 1 for the
first 8
nos, then 2 for next 8, and finally 3 for next 8 and then repeat the
pattern.
Then format conditionally like (after selecting the whole range)
Condition 1 FORMULA IS =$D1=1 WHITE
Condition 2 FORMULA IS =$D1=2 GREY
Condition 1 FORMULA IS =$D1=3 BLUE

I have done it in the following way, you can combine the formulae to
get
into one Col.

Enter in A6
=MOD(ROW()+2,24)

Enter in B6
=MOD(A6,8)

Enter in C6
1
and in C7
=IF(B7=0,C6+1,C6)


Enter in D6
=MOD(C6-1,3)+1

and copy the formulae down (careful for C as you have to copy down from
C7)

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting
on
the
rows. I've seen examples on how to apply it to every other row, but
in
my
case, I have groups of data that remains together in blocks of 8 rows
each
(this is consistent). The other issue, is that I want the formatting
to
start on row 6 (the consistent blocking starts on row 7). I often
need
to
insert another "block" into the spreadsheet and find myself
re-formatting
the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first
block
of 8 rows white, next 8 grey, next 8 blue and then repeat the
conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy

Thank you
--
Cathy


"T. Valko" wrote:

I think you're going to need a macro do that. Try posting in the programming
forum and make sure you explain in great detail what you're trying to do.

--
Biff
Microsoft Excel MVP


"Cathy" wrote in message
...
This worked fine and it formatted my spreadsheet as it exists. The issue
I
experienced was that when I went to add a new block of rows (8 rows) the
row
color was formatted the same as at the insertion point. I would like the
spreadsheet rows to automatically reformat and update when new rows are
inserted. Is this possible?
--
Cathy


"T. Valko" wrote:

If white is the default fill color you only need to test for gray and
blue.

Select the range of cells in question. Assume it's A6:An
Goto the menu FormatConditional Formatting
Condition 1 (gray)
Select the Formula Is option
Enter this formula in the box on the right:

=AND(MOD(ROWS(A$6:A6),24)=9,MOD(ROWS(A$6:A6),24)< =16)

Click the Format button
Select a GRAY fill color
OK

Click the Add button

Condition 2 (blue)
Select the Formula Is option
Enter this formula in the box on the right:

=OR(MOD(ROWS(A$6:A6),24)=0,AND(MOD(ROWS(A$6:A6),24 )=17,MOD(ROWS(A$6:A6),24)<=23))

Click the Format button
Select a BLUE fill color
OK out

--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote
in
message ...
Essentially you need to get the same no. (SAY IN COL D) 1 for the
first 8
nos, then 2 for next 8, and finally 3 for next 8 and then repeat the
pattern.
Then format conditionally like (after selecting the whole range)
Condition 1 FORMULA IS =$D1=1 WHITE
Condition 2 FORMULA IS =$D1=2 GREY
Condition 1 FORMULA IS =$D1=3 BLUE

I have done it in the following way, you can combine the formulae to
get
into one Col.

Enter in A6
=MOD(ROW()+2,24)

Enter in B6
=MOD(A6,8)

Enter in C6
1
and in C7
=IF(B7=0,C6+1,C6)


Enter in D6
=MOD(C6-1,3)+1

and copy the formulae down (careful for C as you have to copy down from
C7)

--
Always provide your feedback...


"Cathy" wrote:

I have a large spreadsheet that I'd like to use conditional formatting
on
the
rows. I've seen examples on how to apply it to every other row, but
in
my
case, I have groups of data that remains together in blocks of 8 rows
each
(this is consistent). The other issue, is that I want the formatting
to
start on row 6 (the consistent blocking starts on row 7). I often
need
to
insert another "block" into the spreadsheet and find myself
re-formatting
the
cell shading all throughout the spreadsheet.

Any ideas on how I could use conditional formatting to make the first
block
of 8 rows white, next 8 grey, next 8 blue and then repeat the
conditional
formatting?





row 1 headers
rows 2 & 3 blank
row 4 (headers) Month, Footage
row 5 (headers) Customer Building, HDD, Customer TYPE
rows 6-13 8 data rows (want them white)
rows 14-21 8 data rows (want them grey)
rows 22-29 8 data rows (want them blue)
repeat this conditional formatting on the color

--
Cathy