XL conditional formatting Q for the gurus

[Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,
Great forum here, have learned a lot.

I have a spreadsheet in which every 5th row contains data that I'd like to conditional format in a simple way (CF any value above 200*).

However, I DON'T want to CF the rows in between, because then the data I'm seeking won't stand out. The sheet contains 1000's of target rows, so I'd rather not use format painter or hand-select each of the hundreds of target rows.

So in English, I'd like to say "CF any cell above 200* in rows 17, 17+5, 17+10..." and so on.

In case this helps, the target rows also have a text label that might helpful in finding them, ie I could CF only rows containing the word "Index."

*Although it would also be nice to be able to put that 200 value in another cell and change it...

Thanks for any help!
-Micah

6 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can conditional format all rows, and one of the condition that you need to add would be row check. taking your example

=AND(A1>200, MOD(ROW()-17,5)=0,row()>=17)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

So the fx above would be the only rule, correct? Can you explain what the pieces do, so I can modify it for my use? One Q is does that fx increment to all rows 5 at a time? Is that what the 17,5 does?

To make the request more specific, here's an example of the data.

A B C D E F G
Row 17 Index 100.0 304.4 105.1 185.4 59.6
So as you know, I'd like to highlight in this case, D17 as the only value >200.

Thx very much, appreciate the help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
yes it is only one condition
A1>200 was your initial condition that you wanted

ROW() is a function that returns the row number

ROW() -17, is to offset the 17 rows. You wanted to start at row 17

MOD is a function that takes two parameters, the first one is number and 2nd one the divisor, and returns the remainder. So MOD(ROW()-17,5)=0 is checking if the remainder is 0, if the row-17 is divided by 5. This was added to highlight only next 5th row (if that needs to be highlighted, which also means if 5th row is not true, and 6th is, it will not highlight either 5th or 6th as we are implicitly saying that conditons needs to be applied only on 5th rows)

ROW() >=17 is to make sure that we start at row 17 and beyond (else there would other rows too that will return 0 as remainder like row 12 (12 -17 = -5 and -5/5 gives 0 remainder)

AND is to say that all these conditions should be evaluated as a group
A1 >200 and MOD(row()-17, 5)=0 and ROW() >=17
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
put your value in a cell suppose it J2

now select the cell you want to use conditional formatting. (e.g. from d1 to h10000)

now apply the conditional fomatting for greater than J2 in all cells.
@rizvisa1 -
Hopefully last dumb Q. Where do we enter this fx?

Thank you.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
it was for conditional format. i take it u already know about it
Yes, maybe it's different in our version. Is this in the "manage rules" dialogue? In our v we have 1 field where we can enter a function.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
yes it is for that. I dont have 2007on this pc so I cannot give you exact steps, but I think you are on right track
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
hi Micah,

it is in New Rule Dailogue,

Click on "Use a Formula to determine which cells to format"

and put the formula in " Format values where this formula is true"