I am trying to get a range of cells to turn a certain color based on the
value of a cell. For example, if the cell at the end of a row in column J is
between 0 and 3, then I want the row to change color to red from A4 to I 4. I
need to do this using a drop down box which I have created. All I can do with
conditional formatting is to turn the color of the box that the number is in.
I am trying to polish up a vendor evaluation spreadsheet whereas the vendors
rating number tells the user whether they are approved (green and between
8-11), use with caution (yellow and between 4-7 ) , or disapproved (red and
between 0-3).

So you want to highlight the row if cell in Col J for that row is between 0
and 3?

Suppose your data is in A1:J100 then select the whole range
in the conditional formatting dialog choose FORMULA IS and enter this
following
=AND($J1=0,$J1<=3)
and choose the format you want...
(note the $ in front of J and its absence in front of 1... this changes the
condition to J2 for row two but keeps it J1 for row1

Change it to if do not want to include 0 and 3
=AND($J10,$J1<3)

"rigid_40" wrote:

I am trying to get a range of cells to turn a certain color based on the
value of a cell. For example, if the cell at the end of a row in column J is
between 0 and 3, then I want the row to change color to red from A4 to I 4. I
need to do this using a drop down box which I have created. All I can do with
conditional formatting is to turn the color of the box that the number is in.
I am trying to polish up a vendor evaluation spreadsheet whereas the vendors
rating number tells the user whether they are approved (green and between
8-11), use with caution (yellow and between 4-7 ) , or disapproved (red and
between 0-3).

highlight A4:I4, With conditional formatting choose formula is and use
this formula =IF(AND($J$4=0,$J$4<4),$J$4,"") and then choose your
colour and adjust the formula accordingly for the other 2!


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31333

Not sure what I am doing wrong here but when I entered the formula as typed,
it changed the whole range that I wanted to apply the formatting to red, and
I had nothing in the drop down box. Think I need to clear the "pick from drop
down list" out of the spread sheet first?
--
Nothing is foolproof to the sufficiently trained and motivated fool.


"Sheeloo" wrote:

So you want to highlight the row if cell in Col J for that row is between 0
and 3?

Suppose your data is in A1:J100 then select the whole range
in the conditional formatting dialog choose FORMULA IS and enter this
following
=AND($J1=0,$J1<=3)
and choose the format you want...
(note the $ in front of J and its absence in front of 1... this changes the
condition to J2 for row two but keeps it J1 for row1

Change it to if do not want to include 0 and 3
=AND($J10,$J1<3)

"rigid_40" wrote:

I am trying to get a range of cells to turn a certain color based on the
value of a cell. For example, if the cell at the end of a row in column J is
between 0 and 3, then I want the row to change color to red from A4 to I 4. I
need to do this using a drop down box which I have created. All I can do with
conditional formatting is to turn the color of the box that the number is in.
I am trying to polish up a vendor evaluation spreadsheet whereas the vendors
rating number tells the user whether they are approved (green and between
8-11), use with caution (yellow and between 4-7 ) , or disapproved (red and
between 0-3).

Blank cells are also treated as 0 so if you have =0 it will highlight
everything...



"rigid_40" wrote:

Not sure what I am doing wrong here but when I entered the formula as typed,
it changed the whole range that I wanted to apply the formatting to red, and
I had nothing in the drop down box. Think I need to clear the "pick from drop
down list" out of the spread sheet first?
--
Nothing is foolproof to the sufficiently trained and motivated fool.


"Sheeloo" wrote:

So you want to highlight the row if cell in Col J for that row is between 0
and 3?

Suppose your data is in A1:J100 then select the whole range
in the conditional formatting dialog choose FORMULA IS and enter this
following
=AND($J1=0,$J1<=3)
and choose the format you want...
(note the $ in front of J and its absence in front of 1... this changes the
condition to J2 for row two but keeps it J1 for row1

Change it to if do not want to include 0 and 3
=AND($J10,$J1<3)

"rigid_40" wrote:

I am trying to get a range of cells to turn a certain color based on the
value of a cell. For example, if the cell at the end of a row in column J is
between 0 and 3, then I want the row to change color to red from A4 to I 4. I
need to do this using a drop down box which I have created. All I can do with
conditional formatting is to turn the color of the box that the number is in.
I am trying to polish up a vendor evaluation spreadsheet whereas the vendors
rating number tells the user whether they are approved (green and between
8-11), use with caution (yellow and between 4-7 ) , or disapproved (red and
between 0-3).

Guys; Thanks so much. I am almost there. I have it doing what I want it to
but it only works for the one row. When I tried applying the formatting for
the first 33 rows, it automatically turned all 33 rows what ever color I
selected for the formatting. Any way to copy the conditional formatting to
the rest of the rows? I dont expect more than 33 vendors per sheet. Sorry if
this seems like a dumb question, but they dont call me a rookie for
nothing.....lol Thanks in advance.
--
Nothing is foolproof to the sufficiently trained and motivated fool.


"The Code Cage Team" wrote:


highlight A4:I4, With conditional formatting choose formula is and use
this formula =IF(AND($J$4=0,$J$4<4),$J$4,"") and then choose your
colour and adjust the formula accordingly for the other 2!


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31333

Thats because when you copied down you left the absolute referencing in
place!, try this copied down:
=IF(AND($J4=0,$J4<4),$J4,"")

The column will remain absolute but the row will be relative.


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31333

Copy any cell which you have formatted.
Select the range you want to format
Choose Edit|Paste Special|Formats

Or you can select the first cell, click on the PAINT icon and paint over the
range.

Remove the = sign from =0 and you will see the difference.
"rigid_40" wrote:

Guys; Thanks so much. I am almost there. I have it doing what I want it to
but it only works for the one row. When I tried applying the formatting for
the first 33 rows, it automatically turned all 33 rows what ever color I
selected for the formatting. Any way to copy the conditional formatting to
the rest of the rows? I dont expect more than 33 vendors per sheet. Sorry if
this seems like a dumb question, but they dont call me a rookie for
nothing.....lol Thanks in advance.
--
Nothing is foolproof to the sufficiently trained and motivated fool.


"The Code Cage Team" wrote:


highlight A4:I4, With conditional formatting choose formula is and use
this formula =IF(AND($J$4=0,$J$4<4),$J$4,"") and then choose your
colour and adjust the formula accordingly for the other 2!


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31333

CCT....Thanks so much. You guys rock!!!!!!!!!
--
Nothing is foolproof to the sufficiently trained and motivated fool.


"The Code Cage Team" wrote:


Thats because when you copied down you left the absolute referencing in
place!, try this copied down:
=IF(AND($J4=0,$J4<4),$J4,"")

The column will remain absolute but the row will be relative.


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31333