How to highlight cells in Excel based on value of another cell

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 ce

How to highlight cells in Excel based on value of another cell

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


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 theHome 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;
    Tips: 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.


  • How to apply color banded rows or columns in Excel?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More... Free Download... Purchase...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

Read More... Free Download... Purchase...Oldest FirstSort comments byOldest FirstNewest FirstComments (16)Rated 4 out of 5 · 1 ratings

Manish

Manishabout 4 years ago#25236This comment was minimized by the moderator on the sitei want to know formula for color change of cell on basis of two different cell valuesReply

crystal

crystal   Manishabout 4 years ago#25237This comment was minimized by the moderator on the siteHi Manish,
Please try this formula =AND($A2="KTE", $B2>100)ReplyReport

Deb

Debabout 3 years ago#28053This comment was minimized by the moderator on the siteHow 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
DReply

crystal

crystal   Debabout 3 years ago#28054This comment was minimized by the moderator on the siteHi Deb,
Sorry can't help you with that. Thank you for your comment.ReplyReport

Aidan

Aidan   crystalabout 2 years ago#28055This comment was minimized by the moderator on the siteHey Deb and Crystal,

For doing it by row, let's say I want to make A red when B > 0.

For that I would go to A, and enter the formula =B:B > 0

That's how you would do thisReply

Thancos Kakinada

Thancos Kakinadaabout 3 years ago#29481This comment was minimized by the moderator on the siteHow can we apply the rule on multiple cells with a single formula? Applying the rule on every single cell will be a hectic job.Reply

Josh Mckeown

Josh Mckeownabout 3 years ago#30264This comment was minimized by the moderator on the siteHo do I apply this rule by row
For example, I want Cell I2 to be formatted by G2. I3 by G3 and so onReplyReport

Aidan

Aidan   Josh Mckeownabout 2 years ago#30265This comment was minimized by the moderator on the siteHey for doing it by row, let's say I want to make A red when B > 0.

For that I would go to A, and enter the formula =B:B > 0

That's how you would do thisReply

ES Alexander

ES Alexanderabout 3 years ago#30478This comment was minimized by the moderator on the siteTo apply this to multiple rows, use the same formula (Excel automatically continues the formula with relative values).
e.g. for the following sheet,
_|_A_B
1| 0 0
2| 0 1
3| 1 0
4| 1 1
to make A1 depend on B1, and A2 depend on B2, and so forth, use =$B1=0. To make all the cells in A depend on one cell in B, use =$B$1=0, and to make the cells in A depend on the cells in the next row of B, use =$B2=0 (with the A cells selected from A1).Reply

Patrick

Patrickabout 2 years ago#31872This comment was minimized by the moderator on the siteHI what if I want to condition a cell based on the presence or not of text in a different cell?Reply

Patrick G Anderson

Patrick G Andersonabout 2 years ago#31877This comment was minimized by the moderator on the sitewhat if I want a cell to be shaded based on the presence or absence of text in a different cell?ReplyReport

Avinash

Avinash   Patrick G Andersonabout 2 years ago#31878This comment was minimized by the moderator on the siteIf you want the condition to format several cells on the same row, select them all and use the formula =$J1="your_text_here"Reply

Shawn

Shawnabout 2 years ago#32044This comment was minimized by the moderator on the siteHow do I solve this ?
Say I have 5 Columns - CORRECT_ANS, PERSON1, PERSON2, PERSON3, PERSON4
and say 4 rows with values as below.

CORRECT_ANS, PERSON1, PERSON2, PERSON3, PERSON4
A B C A A
B A B D B
C C C A B
C A D C C

Values in Columns PERSON1 to PERSON4 should match against corrresponding row in CORRECT_ANS. If value is same, then Cell color to be GREEN, else RED.
So in 1st case, PERSON3 and PERSON4 cell should be GREEN, rest RED. Similarly 2nd row, PERSON2 and PERSON4 gets GREEN, rest RED, and so on.Reply

N

Nabout 2 years ago#33334This comment was minimized by the moderator on the siteI have two column Buy Units (A) and Sell unites (B). When typing sell units B column, If B1>A1 return red font in B1 value. (To say that there is typo= you can not sale more unit than you have got) FOR EACH TRANSACTIONS. Meaning same to be applied for B2>A2, B3>A3., B4>A4... I do not want to keep typing Conditional Formating for each single cell, just want to COPY and Paste. This seems relatively easy ask but taking me a toll! Can you please help?Reply

Nur Siddique

Nur Siddique   Nabout 1 month ago#39082This comment was minimized by the moderator on the siteYes I want learn/ know thisReply

Nicholas Guarino

Nicholas Guarinoabout 2 weeks ago#39679This comment was minimized by the moderator on the siteI just wanted to leave my experience after reading the comments left here when in need of accomplishing entire columns needing to correlate to their respective rows under another column.
I hope I can help out the community for anyone else in search for answers with the same needs as I had.

This example is if you need each cell to act independently according to a cell in its own respective row. For example: Looking at row 4 I need my cell in column H to change color based on a value in column F (still in row 4). Lets say in this case I need H4 to change when the word Complete is entered in F4. (But I need this rule to apply for all rows. -Lets say I have 104 rows total.)

Conditional Formatting: New Rule > "Use a formula to determine which cells to format" - Format values where this formula is true: (enter the following) =$F1:$F104="Complete" (-Choose Format desired)
(We now need to add the cells this new rule applies to, enter the following) =$H$1:$H$104 > Apply > OK

You should now have independent rows formatting cells in column H dependent on the value "Complete" being entered in cells in column F.ReplyRated 4 out of 5There are no comments posted here yetLeave your comments

Guest

Posting as GuestLogin Username PasswordLogin to my account     Name (Required) Email (Required)×Rate this post:Reset0  CharactersSuggested Locations   I agree to the terms and conditionCancel Submit Comment

Video liên quan