XL conditional formatting Q for the gurus
[Closed]
Report
Micahd

RWomanizer
RWomanizer
 Posts
 365
 Registration date
 Monday February 7, 2011
 Status
 Contributor
 Last seen
 September 30, 2013
Related:
 XL conditional formatting Q for the gurus
 Conditional formatting if cell does not contain specific text ✓  Forum  Excel
 Conditional formatting if another cell contains any text ✓  Forum  Excel
 Excel conditional formatting 5 color scale ✓  Forum  Excel
 Copy conditional formatting from one row to another ✓  Forum  Excel
 Excel conditional formatting if cell contains any date  HowTo  Excel
6 replies
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
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)
=AND(A1>200, MOD(ROW()17,5)=0,row()>=17)
Micahd
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.
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.
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
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 row17 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
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 row17 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
RWomanizer
 Posts
 365
 Registration date
 Monday February 7, 2011
 Status
 Contributor
 Last seen
 September 30, 2013
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.
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.
Micah
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.