Excel Conditional Formatting Question 1

[ChiTownDiva]

Happy Monday everybody...

I have an excel spreadsheet wherein if cells in column O falls in between different ranges then I want to format the entire row.

For instance:

If a cell from O12 on is between 1 and 20, then color the row yellow, if it falls between 21 and 34 then color the row green, if it's equal too or greater than 35 then color it red.

I tried to do this via the conditional formatting menu, but it's interpreting each cell individually.

Any help would be greatly appreciated.

Thanks in advance for your help.

ChiTownDiva

 

[xlbo]

You need to utilise Absolute addressing for ranges but I need more info 1st - can it be ANY cell in a row that will colour the row or is the test on a specific column ??

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[ChiTownDiva]

Thanks Geoff..

Actually, it's any value in column O. If the value in cell O25 is 120, then the entire row from A25 to O25 should be red.

Hope this helps.

ChiTownDiva

 

[xlbo]

in which case, select the entire dataset, starting on the 1st row you want to apply the conditional format to and go into the conditional formatting
Change "Value Is" to "Formula Is"

assuming that your start row is 12 then

'Yellow fill
=AND($O12>=1,$O12<=20)

'Green fill
=AND($O12>=21,$O12<=34)

'Red Fill
=$O12>=35

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

btw - this should really sit in the MSOffice forum

Please remember for next time - VBA code questions here - worksheet / formula questions in MSOffice

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[ChiTownDiva]

Unfortunately Geoff, that didn't work for the entire worksheet--just the last two rows...I've tried a numner of formulas to try to get this to work and it hasn't up to this point--which is the reason I'm trying this forum.

I'm thinking I have to accomplish this programically...

ChiTownDiva

 

[xlbo]

nope - not at all. If your dataset is how you have described then the steps I laid out should work

Lets start from the start...
1) what ROW does your data that you want to highlight start in ??
2) the test column is "O" - correct ??
3) If the value in col O falls into the buckets you outlined in your 1st post, you want the entire row coloured ??
4) What COLUMN does your dataset start in ??
5) What ROW does your dataset go up to ??
6) What COLUMN does your dataset go up to ??

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[ChiTownDiva]

Geoff,


1) Row 11.
2) Correct
3) Yes.
4) Column A.
5) Row 112.
6) Column O.

I only need to test the data in Column O and highlight the rows accordingly.

Thanks.

ChiTownDiva

 

[ChiTownDiva]

Thanks Geoff...

I figured it out.


ChiTownDiva

 

[xlbo]

For those that want to know - given the setup above, you should highlight the entire dataset from A11 to O112
Go Conditional formatting and enter the following formulae:

'Yellow fill
=AND($O11>=1,$O11<=20)

'Green fill
=AND($O11>=21,$O11<=34)

'Red Fill
=$O11>=35

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[sedgely]

xlbo
Have just come across this thread and your solution has solved my problem totally. Thanks, have a star.

Cheers, Craig
Si fractum non sit, noli id reficere