# Conditional Formatting Based On Cell Values In A Range?

Jul 30, 2014
I have a range of cells where each row relates to an employee.

In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.

Also it will need to be adapted for "O" , "Y", "X" - each with different colours.

This needs to be repeated on each row for each person.

View 5 Replies
ADVERTISEMENT
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.

View 7 Replies
View Related
Jan 14, 2014

highlighting dates on a premade calendar.

The calendar is made as below:

Tasks

1-1-2014

1-2-2014

1-3-2014

[code]....

The data table is given as below:

Tasks

Date

Task1

1-3-2014

Task2

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.

View 3 Replies
View Related
Mar 1, 2014

I have a series of columns (L - X) each representing a diagnosed disorder (Dx), coded as binary, with 1=Positive Diagnosis (Success). The reference cell contains diagnostic codes; for each respective Dx column I need a 1 or 0, obviously. Each Dx category has a range of values (Dx codes), so I need to write syntax that reflects this range. For example, =IF(A1=>141,AND(A1=<239.99)),"1","0")

In other words, if cell value is 141 through 239.99 then return 1, else 0

Would it be something like this:

=IF(AND(A1=>141,OR(A1=<239.99)),"1","0")

View 1 Replies
View Related
Dec 29, 2013

Here i want to color my cell based on their dates for example : 01-Jan-2014 to 10-Jan-2014.

For the respective date i want to color my bars from G10 to P10.

View 6 Replies
View Related
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.

View 2 Replies
View Related
Oct 22, 2013

what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?

View 6 Replies
View Related
Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting

condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40

condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39

I want to add:

condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40

condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39

and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

View 1 Replies
View Related
Oct 24, 2013

I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:

1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.

2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.

3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.

View 5 Replies
View Related
Mar 9, 2009

scenario: Column H needs to be formatted to green if the text in Column F does not equal "Dog". Dog could be either DOG, Dog or dog. I know how to get it done with only dog, but this one is a bit tricky.

View 2 Replies
View Related
Jun 3, 2014

I have a matrix in excel to show % and $ for multiple columns - set up like this:

Rows Part 1 Part 2 Part 3

Criteria 1 % $ % $ % $

Criteria 2

Criteria 3

the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.

View 6 Replies
View Related
Jan 10, 2014

I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)

Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1))

This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...

1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.

I have 2 rules.

Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM:

It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.

Rule 2: so far this works:

=AND(MOD(ROW(),2)>0)

but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:

=AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents""",ADDRESS(ROW(1),COLUMN())))

View 1 Replies
View Related
Aug 9, 2013

Summary of performance of various products against target is as follows,

Product vs Target

Color Code

Result

CH4OH

Green

1.0

[Code] ........

I need the final result automated as follows,

If 2 green of the 4 products, then final result Gree

If 2 Amber of the 4 products, then final result amber

If 2 Red of the 4 products, final result Red

Is there a way to automate this?

View 8 Replies
View Related
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???

View 3 Replies
View Related
May 9, 2014

I need to highlight a column in a pivot based on the 3 rules below.

Highlight cell in column A if:

Cell in Column A contains numbers 4, 5, 6. or 6.5.

Cell in Column D contains a value

Cell in Column E contains a value

An example of how it should look (Excel 2010): MrExcelhelp1.xlsx

Formula to put in conditional formatting?

View 4 Replies
View Related
Dec 9, 2011

I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.

In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.

I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:

If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.

If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.

If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.

If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.

If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.

If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.

If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.

If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.

If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.

How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.

I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.

View 1 Replies
View Related
Feb 19, 2013

I'm creating an excel document that tracks the amount of time someone has (in months) in the program. Certain residents are able to 'fast track' if they meet conditions, and I am trying to create this spreadsheet so that anyone who looks at it can tell who qualifies (and when).

The issue I'm having is with conditional formatting, because I don't know how to do it with mutliple conditions.

Column C is their previous time (months) in the program

Column D is their current time (months) in the program

Column E is their total time in the program (Sum C+D)

Coumn E is what I would like to format, based on the following rules set out in the program manual:

If previous time is 9 months or more, a resident is eligible to fast track after 6 months current time (format green).

If previous time is less than 6 months, a resident is eligible to fast track after 9 months current time (format green).

I understand that their is an odd gap - but these are the rules currently set out by the program manual, which is what I have to follow.

I'm using Excel 2007 - if that changes anything.

View 3 Replies
View Related
Nov 26, 2013

Attached is a workbook. I have manually highlighted the cells to give idea of what I would like the outcome to be.

Moonah OPS sheet.

In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times.

And for the purpose of this I have selected

B5 as 0800:1621, manually highlighted B11:B27

C5 as 06:00, manually highlighted C7:C24

D5 as 07:00:1521, manually highlighted B11:B27

E5:I55(RDO, REC, SICK, Travel) can remain as manual input if it is to much to work out.

Is it possible or just a pipe dream, to use some method to automatically highlight a range of cells based on the C5:J5 'time'?

If the time is an actual shift time, the colour will be green and dark green writing as default, then we can allocate jobs/training/meetings and manually colour them.

Daysheet DAY MONTH YEAR template - Highlight.xlsx

View 7 Replies
View Related
Oct 19, 2013

I am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.

Everything I keep trying ends up highlighting the entire workbook.

I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.

I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.

View 3 Replies
View Related
Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:

6

6

14

Column L:

3

NA

17

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies
View Related
Mar 12, 2014

I am trying to get a row of cells to highlight a percentage based on a date range

Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.

In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.

Name Start Date End Date Jan Feb Mar

Dale 11/01/14 26/02/14 21 days 26 days

I have attached the spreadsheet for an example : Book1.xlsx

View 3 Replies
View Related
Mar 28, 2014

I have a spreadsheet using Conditional Formatting to colour cells. Is it possible to hide the cell value so only the colour is shown?

View 2 Replies
View Related
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.

View 8 Replies
View Related
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.

View 13 Replies
View Related
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.

View 12 Replies
View Related
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.

View 7 Replies
View Related
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

View 6 Replies
View Related
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.

View 4 Replies
View Related
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.

View 5 Replies
View Related
Feb 12, 2013

I have a range of data on sheet1 say A1:A10 with random letters in each cell. On sheet2, I also have random letters and number in column A. What am trying to do is find a formula which if any of the data on sheet1 can be found in say A1 of sheet2 then shade the cell a given colour or if formula in B2 then show True otherwise false if not found. then do the same for the remainder of the cells on sheet2 which am assuming will be to just copy the formula in B1 of sheet2 or the conditional format in A1 whichever comes first.

View 4 Replies
View Related