I have the following data sheet
E | F | G | H | I |
---|---|---|---|---|
1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 0 |
0.1 | 0.2 | 0.3 | 0.4 | 0.5 |
0.6 | 0.7 | 0.9 | 0.8 | 0.1 |
I would like to implement a conditional formatting formula that would:
- bold the maximum number of each row (i.e.
I1
,H2
,I3
,G4
would be bold); - bold+red the maximum number of each block, where a block consists of multiple rows -
E1:I2
is one block,E3:I4
is another, etc. (i.e.H2
andG4
would be red + bold).
The first rule is easy to implement. I simply apply the condition =E1=MAX($E1:$I1)
on the entire range E:I
.
For the second rule, what I currently have is the condition =E1=MAX($E$1:$I$2)
on the range E:I
, however this only works for the first block. One way to solve it is to apply it to the range E1:I2
instead, then copy the same formula to the range E3:I4
, manually fix the row numbers in the condition, and repeat for every block below. However, there are quite a few blocks below, so this solution isn't ideal. Is there a better way of implementing this blockwise formatting?
EDIT
A note on @doubleunary's accepted answer below - the answer only works for blocks of size 2 (i.e. 2 rows). For larger blocks (in my case of size 6) I had to use the following, more general formula (fortunately none of my cells are empty so I didn't have to worry about that part):
=E1=MAX(OFFSET($E1:$I6, -MOD(ROW(E1) - 1, 6), 0))