how to change a row color based on a text value in a cell

Format Row Based on One Cell

Apply provisional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in cavalcade B are greater than 75, make all data cells in the aforementioned row bluish

Format Entire Row with Conditional Formatting

You tin utilise conditional formatting that checks the value in 1 cell, and applies formatting to other cells, based on that value. For example, if the values in cavalcade B greater than 75, make all data cells in the aforementioned row bluish.

Video: Format Row with Conditional Formatting

Y'all can scout the steps in this video, and the written instructions are below the video. The full transcript is besides available beneath.

Format the Cells

In this example, nosotros'll colour cells blue, if the number of units, in column B, is greater than 75. At a glance, we'll be able to see which rows have a high number of units.

Follow these steps to become started with the provisional formatting rule:

• Select the cells that y'all want to format (A2:D8 in this example)
• On the Ribbon'south Home tab, in the Styles grouping, click Conditional Formatting

• Next, near the bottom of the list, click the New Rule... command

New Formatting Rule Dialog Box

The New Formatting Rule dialog box opens, where you tin can enter the conditional formatting rule details

• In the top department, Select a Rule Type, click on the terminal choice:
  • Use a Formula to Determine Which Cells to Format

The lower section, Edit the Rule Description, changes automatically.

Now it shows a box where y'all can enter your formula:

• Format values where this formula is true

Enter Conditional Formatting Formula

Any conditional formatting formula must exist a test, that gives a TRUE or FALSE result.

For this conditional formatting dominion, the formula will practice a simple check:

• Is the number of units, in column B, greater than 75?
  • True or FALSE

Row and Column Reference

Before you create a conditional formatting rule, cheque which cell is active on the worksheet.

If the formula will have cell references, they might need to refer to the active jail cell's row and/or column

• In this example, cells A2:D8 are selected
• Cell A2 is the active cell
• Formula should check the value in cell B2 -- the active cell'south row
• Formula would work for the active jail cell (A2): =B2>75
• Formula will demand the correct type of reference to cell B2

Relative or Absolute Reference

In Excel, a cell reference tin can be an accented, relative reference, or mixed reference. Which type of reference should be used in this conditional formatting formula?

• Accented: $B$2 -- both column and row are locked -- all cells would check value in worksheet prison cell B2
• Relative: B2 -- neither column or row are locked -- all cells would check value in their row, in column to their immediate correct
• Mixed: B$2 - column not locked, row locked - all cells would check value in cell in row two, in column to their firsthand correct
• Mixed: $B2 - cavalcade locked, row not locked - all cells would cheque value in their row, in cavalcade B

Enter Formula with Mixed Reference

For this formula, we need to use the terminal option, a mixed reference -- $B2. With that reference, the conditional formatting will work correctly, in every cell that is selected, from A2:D8

• In the rule box, enter the following formula:
  • =$B2>75
  • column reference is locked ($B) , row reference is not locked (ii)
  • all cells volition check the value in their own row, in column B

Add together Formatting

The purpose of conditional formatting is to highlight cells that run into the criteria rules.

In this example, the cells should turn blue, if the formula result is TRUE. That volition highlight the rows, so they're piece of cake to spot in a long list.

Follow these steps, to set the formatting:

• In the New Formatting Rule dialog box, click the Format button.
• In the Format Cells dialog box, select the formatting options that you desire.
  • In this example a light blue fill color was selected
  • Yous could also select Font, Border, and Number formatting
• Click OK, to close the Format Cells window

Check the Rule Settings

Back in the Conditional Formatting Rules Manager, the new dominion settings are shown

• Cheque the formula, and the formatting preview
• If everything looks correct, click the OK push, to apply the rule change.

On the worksheet, the new conditional formatting rule is applied.

• Rows with a number greater than 75 in the Units cavalcade, are highlighted with blueish make full colour.

Absolute Reference vs Relative Reference

In the formula, it's of import to use an absolute reference to column B, by putting a $ in front of the cavalcade letter -- $B

• The absolute reference ensures every cavalcade refers to the value in cavalcade B.
• Rows 3 and half-dozen have blue fill, considering they take units greater than 75
• 

Incorrect Highlighting

If nosotros use a relative reference -- B -- without the $ sign, the column reference would adjust automatically, in each column of the selected range.

• Instead of referring to column B, each cavalcade would refer to the cavalcade to its correct.
• That would requite wrong highlighting with the conditional formatting, every bit shown below
  • Cell C2 has blue fill up, because the prison cell to its right is greater than 75
  • Cells B4 and B5 accept bluish make full, considering cells C4 and C5 are greater than 75

Alter Conditional Formatting Rule

After you create a conditional formatting rule, you might want to alter it. For example, in the previous example, a formula-based rule was created, to highlight rows with a 75 or more units.

Instead of setting that number in the conditional formatting rule, nosotros'll alter the dominion, and refer to a cell on the worksheet, where a number is entered.

Existing Conditional Formatting Rule

If you want to change an existing conditional formatting dominion, follow the steps below:

• Select the cells where the original conditional formatting rule was applied -- A2:D8 in this example
• On the Excel Ribbon, click the Abode tab
• In the Styles group, click the Provisional Formatting command
• Adjacent, at the bottom of the listing of options, click on Manage Rules

Provisional Formatting Rules Managing director

The Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells (Current Selection).

• Notation: To see other rules, you can select from the "Show Formatting Rules For" drop down list, at the meridian of the Rules Manager dialog box.

To change one of the conditional formatting rules:

• Select a dominion in the list, and click the Edit Rule button
• In the Select a Rule Blazon section, the current rule type is highlighted
  • In the screen shot below, the dominion type is "Use a formula..."
• In the Edit the Rule Description department, the current settings are shown
  • In the screen shot below, the rule uses a formula: =$B2>75

Change the Dominion Settings

In the bottom section, Edit the Dominion Description, yous can make changes to the rule and its formatting. In this example, the formula will modify:

• from the old rule, with a number: =$B2>75
• to the new formula with a cell reference: =$B2>$F$1

First, follow these steps to change the formula:

• In the formula box, delete the number, 75
• Move the Provisional Formatting Rules Manager, if necessary, to meet cell F1, where the number is stored
• Click on cell F1, to add that prison cell reference to the formula

Next, you lot tin can as well change the formatting for the dominion, if you'd like to. In this example, the rule will have green fill up colour, instead of bluish.

• Click the Format button
• In the Format Cells dialog box, click the Fill tab
• Click on the light green color
• Click OK, to shut the Format Cells dialog box

In the Conditional Formatting Rules Manager, the new dominion settings are shown

• Click the OK push, to use the rule change.

On the worksheet, the new conditional formatting rule is applied.

• Rows where the number of units is greater than the value in prison cell F1 have green fill

Video Transcript

This is the total transcript for the Colour a Row in Excel Based on I Cell's Value video, at the top of this folio.

With Excel'south provisional formatting, you tin hands highlight a cell if it's over or under a sure value, or if it meets a value that you lot've set up.

Merely in some cases, instead of just a single cell, you lot might like to highlight a whole row in a table, if ane of the cells in that row is over a sure number or under.

In this case, we would similar to highlight each row in this listing if the number of units sold is greater than 75.

So to do that, I'm going to select all of the rows, all of the columns in each row. And then I've selected from A2 downwardly to D10.

On the Ribbon, on the Dwelling tab, I'll click Conditional Formatting, and none of these preset rules volition do exactly what I want. So I'yard going down to New Rule, and in here I'll select a formula.

So I'thousand going to utilize a formula to determine how to colour each row.

When I click that, in that location's a spot where I can put the formula.

I desire to, in each row, look at the value that'southward in column B. And so I'll type =

And we want, from every column, we desire to look at cavalcade B. And then we have to lock that cell. We don't want it to be relative, we want it to be accented.

So type a $ to lock that in. So B.

And we want, in this instance, the active cell we can see is white, where the other cells are highlighted with blue.

We tin encounter that, in the name box, A2 is showing upwardly. So that's the active jail cell, so the active row is ii. And so I'one thousand going to type ii hither.

Nosotros're going to check what's in B2 and meet if it'due south greater than 75. So that'south our test.

And if it is greater than 75, nosotros desire to format information technology. Then I'll click Format and I'll choose a make full color, maybe a blue colour and click OK, and click OK once again.

And now, any row where the number of units is greater than 75, all four cells in that row are colored blue.

Get the Sample File

Download a zipped sample file for this Excel tutorial. The xipped file is in xlsx format, and does non incorporate any macros. This example is on the sheet named MultiCell.

More than Tutorials

Conditional Formatting Intro

Fix Provisional Formatting Extra Rules

Provisional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Information Bars

Format Row Based on 1 Cell (2003)

Source: https://www.contextures.com/xlcondformat02.html

Posted by: michaelquithethand.blogspot.com

Share this post