Conditional formatting in a cell formula

[Phil Street]

I am constructing a workbook which will hold a full season's fixtures
and results as the season progresses.

As there are 16 teams in the competition, there are 8 lines per week's
results (home team and away team per line). To simplify things,
basically this is how each week will look:

[Best viewed in a fixed pitch font]

| 1 | 2 | 3 | 4 | 5 | 6
A | Home Team 1 | Pts For | Pts Agst | Away Team 1 | Pts For | Pts Ag
[....]
H | Home Team 8 | Pts For | Pts Agst | Away Team 8 | Pts For | Pts Ag

Using Format | Conditional Formatting, I have set the team which has
the highest score, to be displayed in bold (i.e. cells A1 & A4). The
condition is that if A2 > A3 then make A1 bold. The same condition is
done for A4 i.e. if A5 > A6 then make A4 bold. The purpose of this is
so that the winner of each match can be seen easily as their team name
will be in bold. As there are 16 teams involved, I have to go through
this a further 15 times for each week's fixture. As there are 22
rounds to the season this means I will need to manually conditionally
format 352 cells (phew!). Can I achieve this in a simple cell formula?

Also, if this can be done am I able to copy with auto fill and have
the cell references updated to reflect this? I attempted to auto fill
the first cell (A1 in my above example) only to have the contents of
B1 an exact copy of A1 with comparison done on the cells in Row A (A2
& A3), not Row B as I expected (B2 & B3).

I am a novice at this sort of thing and am hoping to learn as well
find a solution to this problem.

Thanks in advance.
--
Phil Street
aka (e-mail address removed)


Ph (Aus): 0403 166 504 Int: +61 403 166 504

 

[Dick Kusleika]

Phil

Set up CF in one cell, make sure you use relative cell references (i.e.
=A2>A3, not =$A$2>$A$3). Select the cell and Edit - Copy. Select all the
other cells that you want to have the same CF and choose Edit -
PasteSpecial - Formats. Your references, being relative, will update
properly.

 

[Tom Ogilvy]

Select Column A and Column D

with A1 the activecell

go to
Format=>Conditional Formatting
in the formula dialog, change Cell Value is to Formula is

enter the formula

=B1>C1

Then set your formatting.

this should put the appropriate formula in each of the selected cells.

You write the formula relative to the active cell. Since you are using
relative references, then it will be set for the other cells relative to
their location. I suspect you used $B$1>$C$1 which would not allow excel
to adjust the formula.

 

[gromit]

Set up CF in one cell, make sure you use relative cell references (i.e.
=A2>A3, not =$A$2>$A$3). Select the cell and Edit - Copy. Select all the
other cells that you want to have the same CF and choose Edit -
PasteSpecial - Formats. Your references, being relative, will update
properly.

Thanks Dick (and Tom!). I was using Absolute references (which was
what conditional formatting was doing). It's all fine now I changed
the values to Relative references.

Thanks feller!