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
"Max" wrote in message
...
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