Conditional Formatting Problem!!!

ksweat

New Member
Joined
Jan 30, 2008
Messages
31
I can't figure this out, I know how to use conditional formatting to highlight a row, but some of these overlap:

Column D is between 0 and 30, turn green
Column D is between 31 and 45, turn yellow
Column D is greater than 45, turn red

The issue I'm running into is, Column D will always be greater than 0, so they all turn green no matter what. Any suggestions?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I can't figure this out, I know how to use conditional formatting to highlight a row, but some of these overlap:

Column D is between 0 and 30, turn green
Column D is between 31 and 45, turn yellow
Column D is greater than 45, turn red

The issue I'm running into is, Column D will always be greater than 0, so they all turn green no matter what. Any suggestions?
What version of Excel are you using?
 
Upvote 0
You have to do it 3 times as under, set color format as you want:
1. Format only cells that contains: Cell Value: Less than 30
2. Format only cells that contains: Cell Value: Between 31 and 45
3. Format only cells that contains: Cell Value: Greater than 45

Regards
 
Upvote 0
Try this...

Let's assume the range to format is D1:D10.

Select the *entire* range D1:D10 starting from cell D1.
Cell D1 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules

New rule
Use a formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT(D1),D1>=0)
Click the Format button
Select the Patterns tab
Select a shade of GREEN
OK>OK

New rule
Use a formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT(D1),D1>=31)
Click the Format button
Select the Patterns tab
Select a shade of YELLOW
OK>OK

New rule
Use a formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT(D1),D1>45)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top