Conditional Format to Define Group

[cshenoy]

I have two conditional formats I'd like to set up. I would like to use a formula in the conditional format dialogue, if possible. For the first I'd like to draw a box around the scores that are associated with a group. For the second, I'd like to draw a line underneath the last member of the group. The groups have different numbers of members ranging from 3 - 6.

I've attached an example of how I'd like the final product to look like. I've set something up that's similar, but I based it on the row number and it had a regular pattern. Not sure if there's an easy way to put this together. Thanks for your help!

[HansV]

For the table in O1:R11:
- Select this range. I'll assume that O1 is the active cell within the selection.
- On the Home tab of the ribbon, click Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula

=$O1<>$O2

- Click Format...
- Activate the Border tab.
- Set the desired line style and color.
- Click to turn on the bottom border in the sample area.
- Click OK, then click OK again.

I'll have to think about the boxes.

[HansV]

For C3:L12:
- Select this range.

- On the Home tab of the ribbon, click Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula

=AND(C3<>"",OR(C2="",ROW(C2)=2))

- Click Format...
- Activate the Border tab.
- Set the desired line style and color.
- Click to turn on the top border in the sample area.
- Click OK, then click OK again.

Repeat the above steps, but with the formula

=AND(C3<>"",C4="")

and set the bottom border. Also with the formula

=AND(C3<>"",OR(B3="",COLUMN(B3)=2))

and set the left border. Finally, with the formula

=AND(C3<>"",D3="")

and set the right border.

[HansV]

Here is the workbook with the conditional formatting rules.

example.xlsx

[cshenoy]

You are the best! I always learn so much. Thanks!!