Conditional Formatting Based On 2 Criteria
I am trying to figure out how to set up a spreadsheet, that comprises a list of open-entries, wherein the user will insert their information and a % out of 100 assigned to them; the entire form, in the end, cannot go beyond 100%, so each entry has to be done correctly...
BASICALLY my problem is that I am trying to set up the " Total %" cell, which is already assigned the formula "=SUM(D5:D16)" (D5 through D16 being the cells in which %'s are assigned) so that it will auto-populate with the appropriate color/error if the %'s are either insufficient (short of 100%) or exceeding (above 100%). What I had been trying to set up (and have successfully set up in other spreadsheets, however under slightly different circumstances) is a Conditional Format in which the first (with the color Red for "exceeding") was " =If(SUM(D5:D16)>100,SUM(D5:D16),SUM(D5:D16))
" So regardless of exceeding, exactly met, or insufficient totals, it would post the total entered, BUT assigning the color red, so that IF it did, in fact, total higher than 100, it would appear red. A similar formula would be applied to Green (reflecting "on target") in which the formula would be " =If(SUM(D5:D16)=100,SUM(D5:D16),SUM(D5:D16))"
Now the problem is: this is not working for the SUM of the cells. In my previous experience with Conditional Formatting and color assigning results, it had been with the AVERAGE of two columns of cells.(worked without a hitch.)
View Complete Thread with Replies
Related Forum Messages:
IF Statement Using Formatting Criteria (NOT Conditional Formatting)
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Conditional Formatting And List Of Criteria
I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.
Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?
Conditional Formatting Criteria From Another Worksheet
I have a worksheet titled "detail" containing 3 cells which automatically and independently change colour subject to their proximity to various dates. On a new worksheet titled "overview" in the same workbook I would like to have one cell which changes colour if any of the 3 cells in the other worksheet are triggered.
Add Conditional Formatting Criteria With Code
I recorded some code and cleaned it up to apply a conditional format
Dim wbBook As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wbBook = ThisWorkbook
Set ws = wbBook.Worksheets("Summary")
Set rng = ws.Range("C4:P52")
.FormatConditions.Add xlExpression, Formula1:="=C4<0"
.FormatConditions(1).Interior.ColorIndex = 45
I would like to apply a custom color index to all cells in range C4:P52 if the value is less than zero.
But when I execute the macro, many cells receive the custom format that do notr match the custom format. Other cells that should receive the custom format do not
Multiple Criteria Per Conditional Formatting Rule..
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?
Conditional Formatting To Return The Following Results If The Criteria Is Met
I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
Exceeding 3 Criteria Limit In Conditional Formatting
The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells. That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error". Is there a way to get around this? This is the code segment I've tried in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("b2:q53")) Is Nothing Then
Select Case Target
Case "IBBCH", "ibbch"
icolor = 36 'light yellow
Case "OBBCH", "obbch"
icolor = 34 'light turqoise
Case "OBBRDG", "obbrdg"
icolor = 35 'light green
Case "LNCH", "lnch".........................
Formula Within Conditional Formatting To Color Cells Meeting Criteria
I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff. Here is what I want to do: In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSETRUE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
SCRNRTH_SCRM1 TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1TRUETRUE should fail test
Conditional Formatting Based On Date
I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:
If something is past due (date shown is more than 1 year old) or due within 30 days = RED.
If something is due within 31-60 days = YELLOW
If something is due within 61-90 days = GREEN
So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.
If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.
Conditional Formatting Based On Dates
I am trying to use conditional formatting to color a cell based on a date. I have a date in cell "a1" as my base date (no formatting needed). I have dates in cells "a5:a10".
I need those dates (a5:a10) to change color based on the time from "a1".
Less than 1 year = no formatting.
Between 1 year and two years = yellow.
Over two years = red.
I have tried different settings / formulas in the conditional formatting option, but I can't make it work. Do I need to use visual basic to do this?
Conditional Formatting Based On Now Formula
My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.
The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).
What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.
Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...
Conditional Formatting Based Upon Various Dates
I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:
Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:
Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry
Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)
Now here is what I have so far:
3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days
N3 = 913
N4 = 1065
Conditional Formatting on cell C21
Condition 1 - color Green
Condition 2 - color Red
Condition 3 - color Yellow
Conditional Formatting - Color Groups Based On Row Value
This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.
Deleting Rows Based On Conditional Formatting
I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.
Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.
Here's what I already have:
Conditional Formatting Based On Adjacent Column Value
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?
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
Conditional Formatting More Than 3 Conditions Based On Dates
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).
My conditions are:
1. Turn the cell background colour red (action overdue) if:
2. Turn the cell background colour orange (action required) if:
Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
CONDITIONAL FORMATTING TO VBA. Row Format Based On Cell Value
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 5 Named Ranges
i edited this thread becuase I realized why I was getting the wrong results, however, I am completely clueless on how I need to accomplish the task below.
I need to apply formatting based on five named ranges on another sheet. if the values in cell F1:F1000 appears in the named range "datestore" and the corresponding value in range h2:h1000 appears any of the named ranges "date1" through "date5" I would like it to fill the cell yellow.
Conditional Formatting Based On A Percentage Difference
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.
Conditional Formatting Based On Dependant Lists
In the drop-down menu in col D I select the value “Egress”, then the drop-down list in col E is populated with the values of EgressCol. Now I select a value, lets say “Cash payment”. If at a later time I change the value of col D, lets say to “Ingress”, then “Cash payment” in col E doesn’t match the values available for IngressCol (the correspondent dynamic list for the Ingress value).
I need a conditional formatting formula that checks whether the value in col E is listed in the corresponding list of the value selected in col D. Request: I found this formula that works fine, but it gets Excel to work really slow.
Conditional Formatting Based On Cell Content
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......
Conditional Formatting - Based On Another Cells Contents
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.
Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell
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).
Conditional Formatting Based On Publication And Review Dates
I have a spreadsheet that tracks publication dates and review dates. I would like the publication date to change color based upon its relationship with todays date. If the publication date is equal to or older than two years from today turn red, if it is equal to or older than a year and a half from today turn yellow. This seems like a pretty simple formula but I have tried several times and I can't seem to get it to work.
Hide & Show Rows Based On Multiple Conditional Formatting
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:
The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
Conditional Format Based On Multiple Criteria
I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
Fill List Based On Conditional Criteria
I have an Excel sheet with a column (column O) containing expiring dates. The dates represent the day a certain product expires, and for each date, a name is attached (in column A). So there are 500 dates in the sheet, each with a different corresponding name. How would you have Excel create a list, in a new sheet, of the names that are expiring in less than 90 days from today? That is to say, if the date in a cell in column O is less than 90 days from today, then the information in the aforementioned cell and the information in the cell in column A (in the same row) is COPIED (not moved) to a list in a new sheet?
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
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.
Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
Conditional Formatting Based On Cell & Corresponding Cell
1. A1 needs to turn red if a value greater than $2,000 is inserted.
2. A1 will turn back to white if either "John Doe" or "Jane Doe" is inserted in B1.
3. If no value is inserted in A1, A1 needs to remain white.
4. I'll need to do the same for A2-B2, and so on.
That's it. I tried this in C1: =IF(AND(A1>2000,B1="JANE DOE"),1,IF(AND(A1>2000,B1="JOHN DOE"),1,0)).
I then used Conditional Formatting in A1 to point to C1 and turn the red in A1 off or on based on the result. Hey, it worked! Problem is, A1 needs to stay white until a value is entered, and no value entered returns 0 (red), which oddly enough is exactly what I'm telling it to do. Surprise! How do I get A1 to default to white until a value is entered in A1?
Conditional Formatting More Than Once
I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"
In "book2" where I am getting stuck is as follows,
Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??
What I need is to repeat the conditional format over and over again forever is there any code for this I can use?
If/then/else Or Conditional Formatting?
I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?