Conditional Formatting using blocks of rows

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

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
 
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)
 
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
 
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.
 
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
 
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?
 
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.
 
Thank you
--
Cathy


T. Valko said:
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.
Click to expand...
 
Back
Top