NFT: Excel Question

but I have great success just googling in plain English exactly what I am trying to do in excel. I usually have an answer within 5 minutes.

In this case, I did the google and this link seems really good.
Link - ( New Window )

"go google it, asshole" type of response. This stuff does interest me.

Just use Format Painter for the shaded rows and manually go through after every 8 unshaded. It's only 113 rows should take a minute.

In comment 16523935 bigbluehoya said:

Quote:

but I have great success just googling in plain English exactly what I am trying to do in excel. I usually have an answer within 5 minutes. In this case, I did the google and this link seems really good. Link - ( New Window )

Thanks. I tried that and I'm not getting exactly what I need. That will highlight every nth row. I need to highlight every nth group of 8 rows, essentially.

but I don't have it quite right. For this sheet, it was easy enough to do it manually. But, I expect a bigger file coming, which won't be fun to do by hand.

In comment 16523938 GF1080 said:

Quote:

Just use Format Painter for the shaded rows and manually go through after every 8 unshaded. It's only 113 rows should take a minute.

I did, but see my post immediate before this one.

could you set up a dummy column off to the side and have it change values every 8 rows, and then conditional format off of that?

Note, I haven't seriously used Excel in a long time, just a thought.

I have grown to hate excel. You can get complicated with Conditional Formatting but with excel if you copy and paste data it will mess up the formatting. If you used paste special values then you will be find with the conditional formatting. I can come back later of I remember. Feel free to start a new threqd tomorrow if I do not respond by then.

In comment 16523954 robbieballs2003 said:

Quote:

I have grown to hate excel. You can get complicated with Conditional Formatting but with excel if you copy and paste data it will mess up the formatting. If you used paste special values then you will be find with the conditional formatting. I can come back later of I remember. Feel free to start a new threqd tomorrow if I do not respond by then.

One - I did paste values. Two, I did manage it with conditional formatting using this:
=ISODD(CEILING(ROW()-1,8)/8) and applying the formatting to $A$2:$E$113

I remembered it being more difficult (i.e. more nested conditions). But, this was easy.

Go to conditional formatting and select the option for formula. You'll need to do this twice for two different colors.

=isodd(rounddown((row()-2)/8),0)

=iseven(rounddown((row()-2)/8),0)

In comment 16523959 Matt M. said:

Quote:

In comment 16523954 robbieballs2003 said: Quote: I have grown to hate excel. You can get complicated with Conditional Formatting but with excel if you copy and paste data it will mess up the formatting. If you used paste special values then you will be find with the conditional formatting. I can come back later of I remember. Feel free to start a new threqd tomorrow if I do not respond by then. One - I did paste values. Two, I did manage it with conditional formatting using this: =ISODD(CEILING(ROW()-1,8)/8) and applying the formatting to $A$2:$E$113 I remembered it being more difficult (i.e. more nested conditions). But, this was easy.

Scratch that. I used this: =ISEVEN(CEILING(ROW()-1,8)/8)

He was the BBI expert, and one of the most knowledgeable I've ever come across.

robbie... do you have an alternative you like? Or just stuck hating excel but using it heavily?

In comment 16523966 Andy in Halifax said:

Quote:

He was the BBI expert, and one of the most knowledgeable I've ever come across. robbie... do you have an alternative you like? Or just stuck hating excel but using it heavily?

Google Sheets allows you to do way more.

=isodd(rounddown((row()-2)/8,0))

=iseven(rounddown((row()-2)/8,0))

if you create a column to the right, and in the first 8 rows, populate that column with "1".

Then, in the cell following the 8th "1", paste in this formula (in this case, the dummy column is C):

=IF(SUM(C2:C9)=8,0,IF(SUM(C2:C9)=0,1,C9))

Then copy paste that formula down the rest of the dummy column.

That should leave you with a column of binary 1s and 0s which alternate every 8 rows. Then you can use conditional formatting to highlight the 1s or 0s.

There is probably a more direct path, but this should work.

=ISODD(ROUNDUP(COUNTA($A$2:$A2)/8,0))

I use column A as the basis - but make sure whatever column is referenced there has a value for each row, given the COUNTA formula (ID field is best).

=mod(row(),9)=0

In comment 16524011 BrettNYG10 said:

Quote:

=mod(row(),9)=0

I think he wants 8 highlighted, 8 unhighlighted. So mod-wise: =Mod(row()-2,16)<8

But other poster’s solutions work as well

In comment 16523979 robbieballs2003 said:

Quote:

=isodd(rounddown((row()-2)/8,0)) =iseven(rounddown((row()-2)/8,0))

It was even easier. Only have to do it once the way I did it. In conditional formatting the formula is =ISEVEN(CEILING(ROW()-1,8)/8)
Format 1 color and apply to $A2:$E113

I actually ended up inserting 3 more locations AFTER I set this up. the conditional formatting applied correctly, although EXCEL created its own apply to values with a number of different ranges after each insertion.

But, bottom line, that formula works for 8 rows and applies to every other group of 8. No need for a second formula for a different color.

In comment 16524081 Matt M. said:

Quote:

In comment 16523979 robbieballs2003 said: Quote: =isodd(rounddown((row()-2)/8,0)) =iseven(rounddown((row()-2)/8,0)) It was even easier. Only have to do it once the way I did it. In conditional formatting the formula is =ISEVEN(CEILING(ROW()-1,8)/8) Format 1 color and apply to $A2:$E113 I actually ended up inserting 3 more locations AFTER I set this up. the conditional formatting applied correctly, although EXCEL created its own apply to values with a number of different ranges after each insertion. But, bottom line, that formula works for 8 rows and applies to every other group of 8. No need for a second formula for a different color.

Correct. I do this for other reasons. I usually make if more complex. I'll change all the cells to either black or a dark grey. I'll add an "and" function to the formula I put in this thread so that only lines with text will show up. Then I can use borders for all the cells the same as the background cells (either black or a dark grey) as a trick because you cannot use borders with conditional formatting which I still find really dumb.