CODEWITHSUNDEEP
excelif-statementconditional-statementscolor-scheme
RSM
RSM

Reputation: 15118

How to compile conditional 3 color-scale Excel rule

I want to know what I would need to type into the formula box in excel to solve the problem outline below:

Key: *Variable/Cell*

IF *cellnumber* = 0 shade cells red
IF *cellnumber* > (more than) 0 but < (less than) *cell number* shade cells yellow
IF *cellnumber = *cellnumber* shade cells green.

Upvotes: 0

Views: 18790

Answers (1)

Banjoe
Banjoe

Reputation: 1768

You want Conditional Formatting. Pre-Excel 2007 go to the Data menu and click conditional formatting. Excel 2007 or later go to the Home ribbon and click Conditional Formatting. Here is a guide to conditional formatting that shows something similar to what you need.

As for the second and third condition that apply to the value in another cell, you can put a reference in the conditional formatting window similar to "Cell Value : Equal to :" then =$A$1 in the value box.

For 2011: Any quotes below should NOT be typed. This assumes the cell you want to check against is A1.

  1. Select the range you want to be colored
  2. Home Ribbon -> Conditional Formatting -> Manage Rules
  3. New Rule
  4. "Format Only Cells That Contain"
  5. Choose: Cell Value & between
  6. Enter in "0" and "=$A$1" (remember, no quotes)
  7. Click Format -> Fill, choose yellow
  8. Click Ok
  9. New Rule
  10. "Format Only Cells That Contain"
  11. Choose Cell Value & Equal
  12. Enter in "0"
  13. Click Format -> Fill, choose red
  14. Click Ok
  15. New Rule
  16. "Format Only Cells That Contain"
  17. Choose Cell Value & Equal
  18. Enter in "=$A$1"
  19. Click Format -> Fill, choose green
  20. Click Ok

Upvotes: 4

Related Questions