Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Are you using Excel 2003 or 2007.

In 2003 you can do this by selecting the status column and assigning
conditional formatting rules for the 3 conditions. Only that column will be
highlighted. In 2007 you should be able to acheive the entire row formatting

If this post helps click Yes
---------------
Jacob Skaria


"Ricardo" wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Yes it is a classic Conditional formatting (CF).
Select the rows you want to apply CF.
select CF
Cell Value is equal to
and select the cell where the cell value is"complete".
select the proper formatting and OK.
This will change the color of the cells where the cell value is "complete"
but it will not change the entire row color.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Ricardo" wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Pre-select A1:H10

FormatCFFormula is: =$A1="Complete"

Format to Green

Condition2 =$A1="Incomplete"

Format to red.

Assumes column A is the status column. Edit to suit.


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 00:03:02 -0700, Ricardo
wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Ok, unfortunately that conditional formatting, even though it highlights the
whole line does not suit.

For some reason, it now doesn't care what is in the "Status" column and
highlights the line to the first rule

"Gord Dibben" wrote:

Pre-select A1:H10

FormatCFFormula is: =$A1="Complete"

Format to Green

Condition2 =$A1="Incomplete"

Format to red.

Assumes column A is the status column. Edit to suit.


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 00:03:02 -0700, Ricardo
wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Also, i'm using a set of constant values (e.g. A legend) at the top of the
screen for the reference information.

ex. at the top of the screen I have "Complete", "Incomplete" and "Failed".
Then, I conditionally format the screen to reference those cells when looking
at the information beneath. If the cells beneath read the same as the
"Complete" in the legend then it changes the colour. Am I doing this the
wrong way?

"Ricardo" wrote:

Ok, unfortunately that conditional formatting, even though it highlights the
whole line does not suit.

For some reason, it now doesn't care what is in the "Status" column and
highlights the line to the first rule

"Gord Dibben" wrote:

Pre-select A1:H10

FormatCFFormula is: =$A1="Complete"

Format to Green

Condition2 =$A1="Incomplete"

Format to red.

Assumes column A is the status column. Edit to suit.


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 00:03:02 -0700, Ricardo
wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli

Would be really helpful if you posted the cell references rather than "the
top of the screen"

Also which column is the "Status" column?

Example only...................

=$A1=$G$1 where $G$1 contains "Complete" and column A is the "Status"
column.


Gord


On Wed, 1 Apr 2009 16:54:05 -0700, Ricardo
wrote:

Also, i'm using a set of constant values (e.g. A legend) at the top of the
screen for the reference information.

ex. at the top of the screen I have "Complete", "Incomplete" and "Failed".
Then, I conditionally format the screen to reference those cells when looking
at the information beneath. If the cells beneath read the same as the
"Complete" in the legend then it changes the colour. Am I doing this the
wrong way?

"Ricardo" wrote:

Ok, unfortunately that conditional formatting, even though it highlights the
whole line does not suit.

For some reason, it now doesn't care what is in the "Status" column and
highlights the line to the first rule

"Gord Dibben" wrote:

Pre-select A1:H10

FormatCFFormula is: =$A1="Complete"

Format to Green

Condition2 =$A1="Incomplete"

Format to red.

Assumes column A is the status column. Edit to suit.


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 00:03:02 -0700, Ricardo
wrote:

Hi,

I understand that what I am trying to do is a variation of "Conditional
Formatting", however none of the topics I have read cover what i'm trying to
do.

Step 1: Copy data from a table into a preformatted spreadsheet
Step 2: I would then normally have to colour each row depending on whether I
have received the certification for it. In otherwords "complete" "incomplete"
"partial" etc etc.

What I would like to know is if I can set it so that excel automatically
reads the status column and finds "Complete" it will automatically colour
that entire row "green" (generally about 8 rows across).

ex

1 | Complete | 012345 | etc etc - Would automatically become green once
copied from a database into the spreadsheet.

2 | Incomplete | 012346 | etc etc - Would be red.

Kind Regards,

Ricardo Martinelli