Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to shade a cell based on another cell value in Excel?

Supposing you want to shade a cell (says A1) with a certain background color if cell B4 meets the criteria such as has a certain value or the value is greater or less than a specific number. What would you do? This article will show you the method.

Shade a cell based on another cell value with Conditional Formatting

arrow blue right bubble Shade a cell based on another cell value with Conditional Formatting

You can do as follows to shade a cell based on another cell value in Excel.

1. Select the cell (says A1) you want to shade based on another cell value, then click Conditional Formatting > New Rule under Home tab.

2. In the New Formatting Rule dialog box, you need to:

2.1 Select Use a formula to determine which cells to format option in the Select a Rule Type box;

2.2 If you want to shade cell A1 when value of B4 is greater than 500, then enter formula =$B4>500 into the Format values where this formula is true box;

And if you want to shade cell A1 when B4 has a certain value such as “test”, the use this formula =$B4=“test”. You can change the variable in the formula based on your needs.

2.3 Click the Format button to specify a background color;

2.4 Click the OK button when it returns to the New Formatting Rule dialog box. See screenshot:

Then you can see the specified cell is shaded based on the value of another cell as below screenshot shown.

arrow blue right bubbleRelated articles:

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Deb · 3 months ago
    How might i format column N to be shaded according to value in column L? (Eg higher red, lower blue, equals green, no value - no shading)
    Can i write a formula for column N in conditional formatting?

    K L M N
    DD 0.2 0.8

    AM 0.8 1.0

    TB 1.0 1.0

    AM 1.0 1.0

    TB 0.6 0.8

    AM 0.4 0.4

    AM 0.8 0.8

    Any help appreciated
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Deb,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Manish · 10 months ago
    i want to know formula for color change of cell on basis of two different cell values
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi Manish,
      Please try this formula =AND($A2="KTE", $B2>100)