I would like to fill colour the range A6:AV6 with red if the content of cell
AP7 is CR
I need to do this then for about 75 to 80 rows below this so next row would
be looking for CR in cell AP8 etc. If I can do it without code, great. Any
help appreciated.

You can use conditional formatting in excel...
Select Cell A6
From the Toolbar: Format- Conditional Formatting...

Then using

Formula Is, =(CellLocation="CR")

Where CellLocation would be something like $AP$7
So
Formula Is, =($AP$7="CR")

and then select the Format... button and choose the format that you wish to
apply for that condition.

Then if you copy cell A6 and then highlight the range that is supposed to
change colors/formats in relation to cell AP7, pastespecial Format.

Then you can pastespecial into the first cell of the next group, change the
conditional formatting to reference Cell $AP$8 and reperform the copy and
pastespecial.


" Inserting an option button in Word" wrote:

I would like to fill colour the range A6:AV6 with red if the content of cell
AP7 is CR
I need to do this then for about 75 to 80 rows below this so next row would
be looking for CR in cell AP8 etc. If I can do it without code, great. Any
help appreciated.

"GB" writes:

:" Inserting an option button in Word" wrote:

: I would like to fill colour the range A6:AV6 with red if the content of
: cell AP7 is CR I need to do this then for about 75 to 80 rows below this so
: next row would be looking for CR in cell AP8 etc. If I can do it without
: code, great. Any help appreciated.

:You can use conditional formatting in excel...
:Select Cell A6
:From the Toolbar: Format- Conditional Formatting...

:Then using
:Formula Is, =(CellLocation="CR")

:Where CellLocation would be something like $AP$7
:So
:Formula Is, =($AP$7="CR")

:and then select the Format... button and choose the format that you wish to
:apply for that condition.

:Then if you copy cell A6 and then highlight the range that is supposed to
:change colors/formats in relation to cell AP7, pastespecial Format.

:Then you can pastespecial into the first cell of the next group, change the
:conditional formatting to reference Cell $AP$8 and reperform the copy and
:pastespecial.

That will work for one special format case, but are you limited to just three
conditions in any given cell? I'm working on a sheet that will have two
separate columns of readings, and I'm trying to format them as the original
poster is, but in six different categories. (One will require no formatting.
The rest will wind up with a different font color and weight, and different
fill color.)

--
Patrick "The Chief Instigator" Humphrey ) Houston, Texas
chiefinstigator.us.tt/aeros.php (soon to be TCI's 2005-06 Houston Aeros)
LAST GAME: Chicago 5, Houston 3 (April 26)
NEXT GAME: Date/opponent/site TBA in August 2005

I did reply to this, however I don't see it on here yet, and I need to depart.

Basically, yes can do it, but would require VBA coding on the worksheet,
checking for changes in cells, and if the "desired" cell(s) change, then
implement the corresponding format change to the location(s) desired.

I'll check back later to see if my original reply made it to the site.

"The Chief Instigator" wrote:

"GB" writes:

:" Inserting an option button in Word" wrote:

: I would like to fill colour the range A6:AV6 with red if the content of
: cell AP7 is CR I need to do this then for about 75 to 80 rows below this so
: next row would be looking for CR in cell AP8 etc. If I can do it without
: code, great. Any help appreciated.

:You can use conditional formatting in excel...
:Select Cell A6
:From the Toolbar: Format- Conditional Formatting...

:Then using
:Formula Is, =(CellLocation="CR")

:Where CellLocation would be something like $AP$7
:So
:Formula Is, =($AP$7="CR")

:and then select the Format... button and choose the format that you wish to
:apply for that condition.

:Then if you copy cell A6 and then highlight the range that is supposed to
:change colors/formats in relation to cell AP7, pastespecial Format.

:Then you can pastespecial into the first cell of the next group, change the
:conditional formatting to reference Cell $AP$8 and reperform the copy and
:pastespecial.

That will work for one special format case, but are you limited to just three
conditions in any given cell? I'm working on a sheet that will have two
separate columns of readings, and I'm trying to format them as the original
poster is, but in six different categories. (One will require no formatting.
The rest will wind up with a different font color and weight, and different
fill color.)

--
Patrick "The Chief Instigator" Humphrey ) Houston, Texas
chiefinstigator.us.tt/aeros.php (soon to be TCI's 2005-06 Houston Aeros)
LAST GAME: Chicago 5, Houston 3 (April 26)
NEXT GAME: Date/opponent/site TBA in August 2005

Okay looks like my original reply didn't make it here, so I will try to
reconstruct it.

Basically this is what I had recommended to do and to address your concern
of changing a single cell based on matching to more than 3 criteria.

You can use VBA code that is tied to the sheet that has the cells you want
to "watch". You will want to catch changes to the worksheet by having code
in the Worksheet_Change subroutine..

If you let VBA create the subroutine, it will provide a variable called
Target which can contain one or more cells from a range, so you would need to
iterate through each item in the target variable, if one of the cells changed
in the target matches the cell(s) you are wanting to "watch" then you can
have it set the format of the cell you want to change. You may have to setup
your data so that you can do math functions on it, or if there is a row to
row comparison, (i.e., if something in Row 1, Column 1 changes, then change
something in Row 1, Column 2) then the programming is a lot easier.

Anyways, you are correct, at least as far as I am currently able to tell
that there are only a maximum of 3 conditional format tests that can be
performed. So for your case of 6 conditions will require you would to use
VBA code.
Unless someone else knows of a better way.

"The Chief Instigator" wrote:

"GB" writes:

:" Inserting an option button in Word" wrote:

: I would like to fill colour the range A6:AV6 with red if the content of
: cell AP7 is CR I need to do this then for about 75 to 80 rows below this so
: next row would be looking for CR in cell AP8 etc. If I can do it without
: code, great. Any help appreciated.

:You can use conditional formatting in excel...
:Select Cell A6
:From the Toolbar: Format- Conditional Formatting...

:Then using
:Formula Is, =(CellLocation="CR")

:Where CellLocation would be something like $AP$7
:So
:Formula Is, =($AP$7="CR")

:and then select the Format... button and choose the format that you wish to
:apply for that condition.

:Then if you copy cell A6 and then highlight the range that is supposed to
:change colors/formats in relation to cell AP7, pastespecial Format.

:Then you can pastespecial into the first cell of the next group, change the
:conditional formatting to reference Cell $AP$8 and reperform the copy and
:pastespecial.

That will work for one special format case, but are you limited to just three
conditions in any given cell? I'm working on a sheet that will have two
separate columns of readings, and I'm trying to format them as the original
poster is, but in six different categories. (One will require no formatting.
The rest will wind up with a different font color and weight, and different
fill color.)

--
Patrick "The Chief Instigator" Humphrey ) Houston, Texas
chiefinstigator.us.tt/aeros.php (soon to be TCI's 2005-06 Houston Aeros)
LAST GAME: Chicago 5, Houston 3 (April 26)
NEXT GAME: Date/opponent/site TBA in August 2005