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.
View 6 Replies
ADVERTISEMENT
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?
View 6 Replies
View Related
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.
View 2 Replies
View Related
Mar 6, 2014
I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?
View 1 Replies
View Related
Jul 22, 2008
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View 14 Replies
View Related
May 22, 2012
I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time.
that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64
View 9 Replies
View Related
Feb 25, 2009
I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting?
Example)
Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column.
NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08
View 9 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
Apr 7, 2014
I can set a cell to format if its own cell contains an error, but is there a way to conditional format if the next cell over contains an error? For example, turn A1 red if B1 contains an error?
View 3 Replies
View Related
Jun 6, 2012
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
View 2 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
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Jan 9, 2013
In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.
View 3 Replies
View Related
Jul 18, 2014
I've got a list and I need to check if the adjacent cells for a column are empty or not, and if both are empty then format to a certain color. I have a solution but don't know how to implement it into a format fit for conditional formatting.
I have this in the new rule section of conditional formatting
=IF(AND(ISBLANK(OFFSET(I4,0,-1)),(ISBLANK(OFFSET(I4,0,1))),(ISBLANK(I4)=FALSE)),TRUE,FALSE)
If conditional formatting would allow it I would simply change I4 to the entire range, I am very much confused as to how to format this cell to work with conditional formatting. (I4 is the first cell in the list)
Something else is that if I change I4 to I5 or something then moves all the highlighted cells around, and to top it off the first few cells which should obviously be highlighted aren't, even though the rest of the cells which should be highlighted are.
View 7 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
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
View 1 Replies
View Related
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.
View 7 Replies
View Related
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.
View 2 Replies
View Related
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".
View 4 Replies
View Related
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
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?
View 8 Replies
View Related
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.
View 2 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
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?
View 9 Replies
View Related
