I am having a problem setting conditional formatting for multiple cells.
The first condition of the conditional formatting works for the cells but the second condition does not. There is probably an easy fix for this but I can't seem to find it.
I need to conditional format a cell based on it's current value and a value in another cell.
For example:
Each person would have a different target. So if Steve has a figure of over 20 his highlights green, Paul 12 etc.
However each person would not appear in the same row each day.
The Conditional Forumla of =(A1="Steve.Smith")+(C1>="20") works, but then applying to all cells messes it up a bit.
25
Paul.Smith
14
Ian.Smith
15
Jim.Smith
20
John.Smith
18
I use the conditional formatting of duplicates within a column quite often. Now I am trying to highlight duplicates based on multiple cells in the same row. I can't seem to get a formula to make that work. I have four cells in separate columns but the same row, for example H2:K2, H3:K3, H4:K4, etc. I would like to highlight the four cells in that row if there is a duplicate of all four cells in another row. So if one, two or three cells are duplicates in the same column I do not want to highlight any of the cells, it should only highlight the group of four cells if all four cells are identical in another row. Pretty much the same way the default highlight duplicates works, just the value is spread across four cells.
View 2 Replies View RelatedIf you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies View RelatedI want to conditional format based on all of the following cells contain the same text as AT19 or not.
A19 H19 O19 V19 AC19 AJ19
What is my formula?
I have twelve sheets that each have a total score on cell G10 that I want to have copied to a thirteenth sheet in a specific column (F5-16). What would I have to do to make that happen? The G10 cell is an auto-summed cell, if that makes a difference.
Also, is there a specific way to create formulas?
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then
I want "Expected Delivery Date" as green when
- "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when
- "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
I'm using 2007 and want to add conditional formatting to multiple cells.
Say I have 3 columns and 10 rows of data (A1:10 ; B1:10 ; C1:10). I want to add an icon set to everything in columns B and C based on a comparison with the figure on their immeidate left. So B1 is compared to A1, C1 to B1 etc.
When I do it though it compares everything to A1 (formula in the Conditional Formating is =$A$1 and it won't let me remove the $'s). It looks to me like I can't do it and have to add the formatting to every cell indiviually (which is quite a lot).
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
I have designed a live calendar and am trying to highlight selected dates from a column in the calendar. I am able to select single cell in the column and highlight the date in the calendar, But am unable to select multiple dates in the column and highlight multiple dates in the calendar using conditional formatting.
Eg: calender dates are from B5:H9 and multiple dates are in column J10:J13
J column
--------
J10 7/10/2013
J11 7/20/2013
j14 7/29/2013
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
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.
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
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 RelatedI'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.
I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.
There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.
I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.
View 4 Replies View RelatedI need setting up a conditional rule over 2 pages.
Page 1 displays a list of countries.
Page 2 pulls in the RSS feed from the BBC World News website.
I need to find a way to set a rule that will highlight the country on page 1 if it is mentioned anywhere in page 2.
I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?
View 2 Replies View RelatedDespite 'Google is my Friend' and lot of test, I got a bug in my formula. I'd like to colorize date between (Today + 30) and (Today + 60) The line with the xlsBetween operator, fail...
[Code] ......
Existing solution : I know how to bypass this issue, but it's really an awfull solution : fill all column with orange, and then the Conditional formating will overrun the orange color when nedeed. Works but I don't like it ...
I have a spreadsheet that contains a great deal of information, though most if it contains dates. There are some colums that contain the word "NEED" if i don't yet have the date to put in there, and the cell is yellow. What i want to do is set up conditional formatting to change the cell to pink if:
A)the cell contains the "Need"
and
B)The date in the corresponding row from column C is more than 15 days old.
Start dateEnd Date01/01/201101/01/201102/01/201101/01/201110/01/2011
The above is the result that Im trying to get and I just cant work it out,
Basically,
If B is Blank Format Red
If B is populated and A minus B is less than 5 No format
If B is populated and A minus B is Greater than 5 format Red
-- Im sure its easy but I just cant get it
I am having difficulties trying to get some conditional formatting done.
For the below i need to highlight the 2012 YTD Column based on the following conditions, and i cant get the formula correct
GReen = If 2012 YTD meets /beating both 2012 target and 2011 Actual figures =And(D2
I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.
What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.
My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.
I need to employ conditional formatting to text on a spreadsheet - but using more than the 3 colours provided from the Format>Conditional Formatting menu.
I recently purchased the Excel Hacks (100 Industrial-Strength Tips & Tools) book by David & Raina Hawley, Hack #92 details how to get around Excels 3-criteria limit for conditional formatting using VBA, but this solution only describes multiple formatting on numbers, not text. For example, if each letter of the alphabet were entered into separate cells, I would like each to appear in a different colour automatically.
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.
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies View RelatedWhat 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
I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.
So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.
If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)
The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"
Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)
apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.
I have a workbook with multiple sheets. I want to add conditional formatting for a cell on 1 worksheet based on if multiple ActiveX controls are checked (checkboxes) on another sheet. I have 10 checkboxes (now LinkedCells named "G1" -> "G10") on worksheet "Data Entry". The formula should be if all 10 boxes are checked on the "Data Entry" worksheet, cell "A1" on the "Milestone" will be gray. Any clues on how to write the formula for this Conditional Format?
View 1 Replies View RelatedI have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know. Sample worksheet attached.
View 2 Replies View Related