Based on one cell criteria to change the rest of that row to a col

[Rochelle B]

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[David McRitchie]

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.

 

[Rochelle B]

I know how to use CONDITIONAL FORMATING for a cell or a range of cells - but
my problem is I need a whole row to be referenced from a different cell.

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[David McRitchie]

And that is what I gave, but apparently you don't want column
F itself to be highlighted. Since $F2 reference cell column F
it doesn't make any difference which cell on Row 2 is actually
the active cell. Same instructions, same formula, but you have columns G:X
selected.

That's why I emphasize reading my page, in case I miss the point.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I know how to use CONDITIONAL FORMATING for a cell or a range of cells - but
my problem is I need a whole row to be referenced from a different cell.

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[Rochelle B]

OK, I re-read your response and it made sense. but it isn't working. when I
reopen the conditional formating, everything looks as though it should work.
I am not understanding why it isn't.

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[Rochelle B]

wow i feel so blonde, I got it!!!! Thanks a bunch!!

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[Rochelle B]

Thanks David, I had to read what you said a couple of times - sorry about
that.

It worked!! THANK YOU

And that is what I gave, but apparently you don't want column
F itself to be highlighted. Since $F2 reference cell column F
it doesn't make any difference which cell on Row 2 is actually
the active cell. Same instructions, same formula, but you have columns G:X
selected.

That's why I emphasize reading my page, in case I miss the point.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I know how to use CONDITIONAL FORMATING for a cell or a range of cells - but
my problem is I need a whole row to be referenced from a different cell.

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.

 

[David McRitchie]

Hi Rochelle,
You're welcome.
It takes a bit of getting used to .
but the really good news is that once you understand Conditional Formatting
you will find that Cell Validation and Filtering are very similar.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks David, I had to read what you said a couple of times - sorry about
that.

It worked!! THANK YOU

And that is what I gave, but apparently you don't want column
F itself to be highlighted. Since $F2 reference cell column F
it doesn't make any difference which cell on Row 2 is actually
the active cell. Same instructions, same formula, but you have columns G:X
selected.

That's why I emphasize reading my page, in case I miss the point.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I know how to use CONDITIONAL FORMATING for a cell or a range of cells - but
my problem is I need a whole row to be referenced from a different cell.

:

If there is to be only 3 colors involved you can easily do that
with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm <==== read this
if more than 3 colors look for the link to Extended Conditional Formatting (addin)
Another choice is possibly a Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

For Conditional Formatting you would select the columns F:X then make F2 the
active cell. It would be easier to make F2 the active cell then select all cells with
Ctrl+SHIFT+SpaceBar (or Alt+A before 2003) which will leave F2 as the active cell
and allow you to color the entire row rather than just columns F through X.

Format, Conditional Formatting
C.F. Formula 1: =$F2="Model" format with pattern color of your choice

Logically the formula will be replicated to every cell that selected with you
enter the formula. Physically and internally there is probably only one definition so it doesn't
matter if you select one cell, lots of cells, columns or the entire sheet. So I would
suggest defining what you want and avoid using Paste or the Format Painter tool
to extend something later, but then one never knows for sure how things are done that you can't see.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I am trying to find a formula that will change a whole row to color based on
the critera entered in the In the row F2:F22 column. In other words, say if I
entered MODEL in cell F15, all cells from G15 to X15 over, will turn a color
I specify.