Conditional Formatting Based On Difference Value Between Figures In Two Cells?
Apr 10, 2014
I would like to use conditional formatting to mark cells with arrows depending on the difference between the values in those cells and figures from different cells. Namely, I have percentage values in column A and B. I need cells in column B to be marked with arrows (up, right or down) when the difference between value B and A is bigger than 2%, bigger than 0 but smaller than 2%, and smaller than 0 respectively (see the spreadsheet attached).
View 2 Replies
ADVERTISEMENT
Apr 3, 2009
Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.
An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)
I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.
View 5 Replies
View Related
Feb 18, 2014
I have an excel sheet for recording the results from measurements made on round material.
What I want is to have the conditional formatting so that if they have more than the allowed difference in a column they are highlighted red.
Example:
Allowed difference is 0.06 so the cells with a larger difference would be highlighted red.
580.09
View 1 Replies
View Related
Feb 28, 2007
I would like to put a format on all cells that are below -15:00 (that's the
cell format).
So, e.g. -20:00, -50:00 but NOT -10:00.
Do I use greater or less than? I cant seem to get any to work!
I have already put a format on the cells for greater that 15:00 which works as it's whole numbers - although it has changed the 15:00 in the conditional formatting cell to 0.625 but still seems to work?
View 9 Replies
View Related
Aug 18, 2014
On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies
View Related
Mar 30, 2014
I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies
View Related
Nov 17, 2011
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
Sep 17, 2009
I am using conditional formatting to make cell background's go red if the data satisfys some criteria
Now I want to add the values in those red cells, can anyone make a suggestion as to how I can do this.
View 6 Replies
View Related
Nov 14, 2013
I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be
John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)
How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.
So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.
I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.
View 7 Replies
View Related
Sep 19, 2012
I'd like to format (colour fill) C1 when H1 contains the word "Bills"
I've tried to "use a formula to determine which cells to format" but it keeps using absolute references ($H$1).
Ideally I'd like to apply the "format" to the entire column C.
I did think I'd be able to use OFFSET but it's not working.
View 1 Replies
View Related
Apr 27, 2007
I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.
What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.
View 8 Replies
View Related
Jan 8, 2014
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
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
May 23, 2013
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies
View Related
Jul 19, 2012
I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.
I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.
To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.
View 9 Replies
View Related
Jul 28, 2013
I have a spreadsheet where I want to be able to Fill cells green in Column B to Column N for any Rows that have a value in Column L. All other Rows should remain the current colour (orange).
I can Fill the cells in Column L using conditional formatting but am not sure if I can use Conditional Formatting on other Columns so that their Fill colour is determined by the value in Column L?
View 3 Replies
View Related
May 25, 2013
I have a sheet where i need to highlight cells C3:C140 based on approaching dates. The cells C3:C140 contain dates generated from another file and would be random approaching dates.
The conditions are
1) Approaching Weekends (Sat and Sun) = always turn red
2) 3 days after today = orange
3) 2 days after today = dark orange
4) Other days = light orange
View 11 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
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
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
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
May 31, 2013
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
View 3 Replies
View Related
Feb 10, 2010
I have to calculate the difference of two cells in an excel sheet if the first cell value is greater than the second one. If the first cell value is smaller than the second one then the difference should be shown as zero.
View 9 Replies
View Related
May 17, 2009
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best:
A1 = 5
A2 = 0
A3 = 7
The function i am using looks something like this:-
=IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well.
Maybe it should look something like this but it does not work the way i am doing it.
=IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View 5 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
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Jun 3, 2008
I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.
COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.
So, I enter the following;
A1 B1
21.0 13.0 <----------------No color Blue
I enter new values
21.0 9.5 <----------------B1 is blue.........................
View 2 Replies
View Related
May 14, 2008
I have dates in Column D (mm/dd/yyyy). I'm trying to write a macro to compare the date in any cell in Column D to the current date. Then, if the date in that cell is within a month of the current date, color it red. If the date is within two months, color it orange. Etc, for up to six months. I've read up a little on dates in VBA, but I'm pretty lost.
View 4 Replies
View Related
Feb 2, 2012
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
View 3 Replies
View Related
