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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:71740450eb94c@uwe...
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
--
Message posted via http://www.officekb.com