Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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

Conditional Formatting using blocks of rows
 

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