Re: Conditional Formatting using blocks of rows

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 Format>Conditional 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 news:8A11D7A3-9B12-4F15-B8E9-21BC5423E67D@xxxxxxxxxxxxxxxx
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 spread*** 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 spread*** and find myself re-formatting
the
cell shading all throughout the spread***.

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


.

Quantcast