# Conditional Formatting Based On Cell Data?

Jan 29, 2014

I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.

In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.

The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.

I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.

## Conditional Formatting - Data Bars Based On Value From Another Cell Using INDIRECT

Apr 11, 2014

I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.

Basically I want to show a Data Bar in a certain cell based on the value from another cell.

It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.

As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.

For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.

So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.

If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.

Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?

## Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

## Conditional Formatting On Cell Based On Value

Jul 29, 2014

In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.

## Conditional Formatting Based On A Value In A Different Cell?

Apr 9, 2014

I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.

## Conditional Formatting Of A Row Based On A Value In A Cell Of That Row

Feb 17, 2010

I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to \$20 the entire row would be highlighted with a color.

the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.

## Conditional Formatting Based On Value Of Another Cell

Jun 4, 2012

I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:

A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"

However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.

## Conditional Formatting Based On % In Other Cell

Feb 15, 2013

I want to color format a cell based on a percentage value in another cell but I just can't get my head around how to solve it. I've attached a file that explains it better.

E.g.
The formatting for A1 should be something like:
If A2 is
> +/- 10% RED
+/- 6-10% YELLOW
+/- 5% GREEN

Attachment 214464

## Conditional Formatting - One Cell Based On The Value Of Another

Jan 6, 2014

I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.

## Conditional Formatting Based On Cell Value

Apr 17, 2014

I am manually formatting column J depending on what data is entered into column K. If the number entered into the cells in column K are equal to or less than what is in column J, I format the data in the cells in column J with the green you see above. If the data entered in the column K cells are greater than column J, I format it in the red shown above.

So, how can I have Excel do this for me, format column J cells depending upon what is entered into column K cells? The caveat here is that I also don't want any formatting to occur until data is entered into column K.

## Conditional Formatting Based On Value Contained In Another Cell

Apr 9, 2014

I have some problem with conditional formatting, basically what I'd like to do is color the cells, based on the value contained in one specific cell. I'm not able to make a formula that suits my job.

Basically I want to color code a cell if the value in a specified cell is between x and y.

I've attached a sample spreasheet: MrExcel.xlsx‎

## Conditional Formatting Based On Two Cell Results

May 6, 2009

I have a cell which I want to colour green if C6>9, yellow if O6>60 and red if BOTH C6 and O6 are above their respective numbers. The first two parts of this are obviously straight forward, but I cant work out how to get the third part of it to work.

## Conditional Formatting Based On Text Of Another Cell

Jul 30, 2009

I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed".

## Conditional Formatting Based On Two Cell Values?

Sep 21, 2013

I want to do conditional formatting based on two cell values.

i want to do conditional format the column 'R' based on clolumns 'J' and 'R'.
column 'J' is a text column named PRIORITY which can hold the values P1,P2,P3 an P4.
Column 'R' is nothing but difference of two columns 'M' and 'H'(both are time stamps).Column'R' is in below format.
=TEXT(M4-H4,"h:mm:ss").

column M and H are in below format:

eg: 9/11/13 9:41 AM

Now i want to format the column'R' like
if column J ="P2" and column 'R' > "00:10:00"(10 mins) =it should become red.
if column J="P3" and column 'R'>"00:30:00"(30 mins) = it should become red.

## Conditional Formatting Based Upon Format Of Another Cell?

Mar 25, 2010

Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?

## Conditional Formatting Based On Adjacent Cell?

Mar 4, 2012

I want the font of a value in a cell to turn green or red based on the value of the cell below it - NOT based on a set value or range like all of the other examples are based on. I want to apply a formula or condition to the entire table.

Column a has the date for each value, newest date is row 1 and oldest date is row 250. Column b-k has the number value for each animal species we are tracking. If there are more animals than the day before, the value went up and I want the font to be green. If there are less animals than the day before, then value went down and I want the font to be green.

There are about 250 rows in each table and I have 4 tables. I thought I could do conditional formating but I can only find how to change font color based on a set value. For instance, for each day there are over 10 turkeys, then it would be green. That is NOT what I want, see below:

Mon 5 no color change
Fri 5 color change to green
Thu 4 color change to red
Wed 5 color change to red
Tue 7 color change to green
Mon 5

date looks more like this - col a is date, col b is turkey, col c deer, etc

Mon 5 4 3 4 2
Fri 5 3 4 2 7
Wed 5 2 7 1 0
Tue 7 4 6 5 9
Mon 5 1 1 5 4

I would think this is similar to daily stock prices, if data goes up then it is in green, but if the price goes down then in red. Not based on purchase price (set value), but based on previous days closing price. For this report, I only care about the change day to day.

## Cell Formula Based On Conditional Formatting?

Dec 3, 2012

Say I have the following....

12345678910
59465465495652505853

I conditional format the top number which in this case is 65, how do I highlight the number directly above it? Either by another conditional format or some formula that picks the number out. I've tried HLOOKUP and VLOOKUP, but its giving me spurious results.

## Conditional Formatting Based On Adjacent Cell

Aug 26, 2013

Is there a way to shade a cell based on whether the adjacent cell is shaded? I found some ways to do this with VBA but I was looking for a non VBA solution if possible. So basically if cell C5 is highlighted yellow I would want cell D5 highlighted yellow too, regardless of the values inside the cell. Is this possible without VBA?

## How To Do Conditional Formatting Based On Two Cell Values

Jan 14, 2014

highlighting dates on a premade calendar.

The calendar is made as below:

1-1-2014

1-2-2014

1-3-2014

[code]....

The data table is given as below:

Date

1-3-2014

2-14-2014

I need to highlight the cell in the calendar that matches both the task and the date in the data table. For example- in the situation above, D1 would be highlighted.

## CONDITIONAL FORMATTING TO VBA. Row Format Based On Cell Value

Apr 19, 2008

I have set 3 conditional formatting rules where the row from column A to K will change depending on the value on column L.

Selection is equal to A3:K100, conditions are similar to:
- Formula is =\$K3="PIP"

Then for formatting I chose WHITE BOLD FONT & BLUE SOLID PATTERN, for example.

Conditional formatting only allows 3 conditions. If this can be replicated
using VBA, then you can set "n" conditions, correct?

## Conditional Formatting Based On Time Of Day In Cell

Jul 27, 2006

I would like to use Conditional Formatting to format a cell depending whether it past a certain time, such as 3:45 PM. Naturally, entering the time as the condition does not work. Is there a way to do this?

## Conditional Formatting Based On Cell Content

Jan 3, 2008

I am trying to format cells based on what is in them across the board. Is there a way to conditional format based on what is in certain cells? I have 5 columns. I need to color in every NA only in the rows that have 1 or less cells with a number in there. So if there is 2 cells in that one row that have a number in them then leave the whole row white. If there is only 1 number in that row and the rest are NA then color all the NA's in red......

## Excel 2010 :: Highlighting Data Based On Conditional Formatting

Apr 1, 2014

I'm using Excel 2010 on my Mac. I would like to set up the data as follows:

A
B
C
D
E
F

NAME
DATA X
DATA Y
DATA Z
DATA XX
DATA YY

NAME
90%
88.2
410
88.4%
97.8

[Code] ........

Now what I would like to do is have the data in each column highlighted a certain color based on specific ranges. For example in Column B I want anything from 92% - 100% highlighted blue. I would like 83% - 91.9% highlighted purple, and anything under 83% highlighted red. Do I need to have a conditional format entered in each cell? Can I set up a conditional format for the whole column? Once I can figure the formatting in my example the remaining columns will be a breeze since they will be set up in the same manner just different number ranges, same colors will apply.

## Conditional Formatting Columns Based Off Cell Input

Jun 24, 2014

Instructions are in the attached excel as well. Please see attached

If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.

=Column B>Column C, then turn input cells in Column A Red.

First four highlighted are examples of thise formula scenario

** All based off an input into column

How to use conditional formatting to do so???

Excel Conditional Formatting 06.24.14.xlsx‎

## Conditional Formatting Based On Text In Adjacent Cell?

Feb 16, 2014

A2:A5 contains different dollar amounts, a6 is the sum of those amounts

In column B (B2:B5), i place a "p" beside the amount in column A once it has been paid.

I want to:

a. conditionally format the amounts in column a to turn green once I put the 'p' in the adjacent column

b. conditionally format the sum in A6 to exclude amounts that have been paid, and only display the total amount of the unpaid lines.

## Conditional Formatting Based On Adjacent Cell Not Being Blank

Mar 6, 2014

I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?

## Conditional Formatting Based On Part Content Of A Cell

Sep 10, 2009

I am using excel 2000. I want to apply conditional formatting to a range of cells if they contain (V) as part of the cell contents. The cells will generally look like this : 09.00-6 (V) or 9-5.30 (V). I want to shade any cell if part of the contents of that cell is (V).

## Conditional Formatting Based On Internal Cell Comment

Feb 19, 2013

Can I set the conditional formatting to highlight those cells, which contain internal (not pop-up) cell comment?

For example =10+N("free text comment"). The displayed value is 10, and I need this cell to be highlighted automatically.

The sheet has over 200 columns, so functions based on other cell values are not applicable.

## Using Conditional Formatting To Format Range Based On One Cell

May 20, 2009

I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in \$A1 = 1 then bold \$B2:\$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.

## Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).