In Excel you want to highlight any value over 100 in a range of cells by changing the cell color. How we can do this?

select the cells, click home, click conditional formatting, select highlight rules, select Greater than,

type in the box 100.

 

Highlight values greater than =A1>X

Note: Excel contains built-in "presets" for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article.

If you want to highlight cells that are "greater than X" with conditional formmatting, you can use a simple formula that returns TRUE when a cell value is greater than X. For example, if you have numbers in the cells B4:G11, and want to highlight cells with a numeric value over 100, you select B4:G11 and create a conditional formatting rule that uses this formula:

=B4>100

It's important that the formula be entered relative to the "active cell" in the selection.

To highlight cells less than 100 with a conditional formatting formula, use:

=B4<100

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =A1>100 is evaluated for each of the 40 cells in B4:G11. Because A1 is entered as a relative address, the address will be updated each time the formula is applied. The net effect is that each cell in B4:G11 will be compared to 100 and the formula will return TRUE if the value in the cell is greater than 100. When a conditional formatting rule returns TRUE, the formatting is triggered.

Using a another cell as an input

Note that there is no need to hard-code the number 100 into the rule. To make a more flexible, interactive conditional formatting rule, you can use another cell like a variable in the formula. For example, if you want to use cell G2 as an input cell, you can use this formula:

=B4>$G$2

You can then change the value in cell G2 to anything you like and the conditional formatting rule will respond instantly. Just make sure you use an absolute address to keep the input cell address from changing. Another way to lock the reference is to use a named range, since named ranges are automatically absolute. Just name cell G2 "input" then write the conditional formatting formula like so:

=B4>input How to use Conditional Formatting to Highlight Data

LAST UPDATED ON 3-APR-2015 BY ANNEH

Walk into most offices and you’re apt to find someone using an Excel spreadsheet. It’s one of those common business tools. The problem with many spreadsheets is they make the reader find the key information rather than using Excel to highlight these cells. Fortunately, Excel’s conditional formatting can make your data automatically stand out. (Check Resource section at bottom for video tutorial.)

I see it happen all the time. You get a spreadsheet from someone with rows of data and it all looks the same. But is the data the same? Are there cell values that are different from the rest? Is something outside the norm? These are the type of questions Excel users have when they see spreadsheets.

Formatting Excel Cells Based on Conditions

Instead of having the reader scan each cell, you can have Excel do some of the legwork by using conditional formatting. This allows Excel to apply a defined format to cells that meet specific criteria. These formats might include a different background color, font color or border. The goal is to make important information stand out so you can find them easier.

Excel already does some of this for you. As example when you format numbers, there are options such as displaying negative numbers in red. This is an example of a predefined format.

Excel allows you to use formatting on individual cells or rows. There are two methods involved. The simplest method is to have Excel apply the conditional formatting if the cell meets a certain criteria. This uses the “Cell Value Is” method.

Some Cell Value examples include:

• Apply a red background if the value is less than 50
• Apply an italic bold font if the value is between 70 and 90
• Apply a green font color if the value is Montana

Excel also allows you to use formulas for conditional formatting. One benefit to excel formulas is you can reference the values in another cell. In the example below, I’m requesting that if the value in F2 is greater than the value in H2, apply a green background color. This can be useful if you like to do comparative analysis as you can color code items that fall above or below certain ranges.

You don’t need to apply the formatting to one cell. You could apply conditional formatting to a row or column. The only difference is you highlight multiple cells instead of one when you start the process.

The Order of your Conditions Matters

When you apply the conditional formatting using the “Cell Value Is” method, you’re allowed up to three conditions. For example, the screen snap below shows three rules based on cell values and colors.

• Cell value < 100 format for green background
• Cell value < 50 format for yellow background
• Cell value < 10 format for red background

These formatting conditions seem simple. You’re probably thinking that if you have a cell with a value of 10, it would show with a red background since it is less than 25. Sorry, the value of “10” will produce a green background.

The reason the green background would display is that Excel takes the value of “10” and applies it to Condition 1 to see if it is true. Since 10 is less than 100, the condition is met and the green background is applied. Conditions 2 and 3 are not evaluated. Excel stops when it meets a true condition.

There are two ways to fix the problem. One way is to reverse the order of the conditions so the most restrictive is at the top. For instance, have Condition 1 be Cell Value Is less than 25. A second way would be to change the “less than” criteria to “between” and state the ranges for each of the conditions.

How to Apply Conditional Formatting

Adding these formatting rules is easy. The hardest part is finding out what you want to emphasize on your Excel spreadsheet. What are your audiences’ interests and what actions might they take.To apply conditional formatting to one or more cells,

1. Highlight your cell or range of cells.
2. From the Format menu, select Conditional Formatting…
3. In the Conditional formatting dialog, select Cell Value Is in the first drop-down box.
4. Select a cell evaluator from the second drop down box. Your dialog may change and add another field.
5. Enter your values in the remaining text boxes.
6. Click the Format… button.
7. Choose your format options from the Font, Border and Patterns tabs.
8. Click OK.
9. Click OK or add another condition.

By Conditional Formatting Command

agree with expert answers above

 

Use the conditional formatting or write VBA code (if function changing color index of target cells)

           We can highlight the values by the option of conditional formating.Select the cells and from the conditional formatting select hilight cell rules  option and set the value limit as  hundred and choose the prefered colour for the hilighted cells.

It can easily be done with conditional formatting, in conditional formatting 1st select the cell where you want to highlight the value 2nd select the Highlighted Cell Rules then select Greater then option a popup window will be opened  type value i.e. 100 and then press enter.

using conditional formatting

By using conditional formatting :

 

http://www.excel-easy.com/data-analysis/conditional-formatting.html

Use Conditional formatting for Highlighting