Conditional Format - Outline Border

[Sam via OfficeKB.com]

Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the 1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B" for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank >=10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels, "E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

 

[Max]

This would bring you close to your intents ..

Select E2:I11, then apply CF using the formula:
=OFFSET(INDIRECT("E"&INT((ROW(A1)-1)/2)*2+2),,COLUMN(A1)-1)=$B2
Format fill color to taste > OK out

(I couldn't find a way to color the border around
each of the "2 cells" group in the manner that you wanted)

 

[T. Valko]

Do we need to deal with possibly empty cells in columns B and E:I?

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Biff

 

[T. Valko]

This seems to work and accounts for empty cells. I'm assuming a rank is not
duplicated.

Select the range E2:I11
Goto Format>Conditional Formatting
Formula Is:

=OR(AND(MOD(ROW(),2)=0,COUNTIF($B:$B,$B2)=2,OFFSET(E2,1,,)<>"",$B2=E2),AND(E2<>"",MOD(ROW(),2)=1,COUNTIF($B:$B,$B2)=2,$B2=OFFSET(E2,-1,,)))

Click the Format button
Apply the border of your choice
OK out

Biff

Do we need to deal with possibly empty cells in columns B and E:I?

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Biff

 

[Max]

Select E2:I11, then apply CF using the formula:
Ensure E2 is the active cell in the selection E2:I11
(The suggested CF formula points to E2, and will be "propagated" from there
to the full range E2:I11)

 

[T. Valko]

I couldn't find a way to color the border around each of the "2 cells"

group in the manner that you wanted

Think you'd have to format every other row like 2,4,6 then 3,5,7 and set row
2,4,6 to have no bottom border and set row 3,5,7 to have no top border. I
didn't even think that's what he wanted but maybe it is since in his sample
data the borders don't really stand out and distinguish which set of numbers
is which.

Your formula works but trips on certain situations where there are empty
cells.

Biff

This would bring you close to your intents ..

Select E2:I11, then apply CF using the formula:
=OFFSET(INDIRECT("E"&INT((ROW(A1)-1)/2)*2+2),,COLUMN(A1)-1)=$B2
Format fill color to taste > OK out

(I couldn't find a way to color the border around
each of the "2 cells" group in the manner that you wanted)

 

[Sam via OfficeKB.com]

Hi Max,

Thank you very much for your assistance. Your Formula does the job Great!

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you for your input and assistance.

Do we need to deal with possibly empty cells in columns B and E:I?
No

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Yes. Will I need a different Formula to Max's to take care of situations
where there will be other Numeric Labels with the same Rank value?

Please advise

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you for your assistance.

This seems to work and accounts for empty cells. I'm assuming a rank is not duplicated.

Could your Formula be adapted to take into account duplicate Ranks (other
Numeric Labels having the same Rank)?

Select the range E2:I11
Goto Format>Conditional Formatting
Formula Is:

Click the Format button
Apply the border of your choice
OK out

Further assistance very much appreciated.

Cheers,
Sam

 

[Max]

Welcome, Sam. Good to hear that.

 

[T. Valko]

Ok, if we don't have to be concerned with empty cells we can reduce the
formula to:

=OR(AND(MOD(ROW(),2)=0,$B2=E2),AND(MOD(ROW(),2)=1,$B2=OFFSET(E2,-1,,)))

And it will handle multiple duplicates.

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Brilliant! Very much appreciated.

Cheers,
Sam

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Brilliant! Very much appreciated.

Cheers,
Sam