Conditional Formatting Formula Based On Text In Two Cells?
Jan 8, 2014I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies
ADVERTISEMENT
Conditional Or IF - Formatting Another Cells Font Based On Different Cells Text
May 3, 2013What 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
Conditional Formatting To Colour One Cell Based On Text From Range Of Cells?
Oct 22, 2013what 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 RelatedExcel 2007 :: Conditional Formatting And Adding Text To Cell Based On Multiple Cells
May 31, 2013I'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.
Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells
Feb 5, 2013how 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
Conditional Formula Based On Another Cells / Rows Text / Number?
Jan 25, 2013I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
Conditional Formatting Based On Text Of Another Cell
Jul 30, 2009I 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 RelatedConditional Formatting Based On Text Values.
Mar 9, 2009scenario: 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 RelatedConditional Formatting - Formula=text
Sep 16, 2009I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -
View 3 Replies View RelatedConditional Formatting Based On Now Formula
Feb 19, 2010My 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 On Text In Adjacent Cell?
Feb 16, 2014A2: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.
Conditional Formatting Based On Multiple Text Criteria?
Mar 1, 2014I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.
To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.
However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.
Traffic Light Conditional Formatting Based On Text?
Nov 4, 2013I'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.
View 2 Replies View RelatedConditional Formatting Based On Color Or Text Of Another Cell
Jun 14, 20131 2 3 4 5 6
1
2 x x x x
3 x x
4 x x
5 x x
6 x x
Assume that is the table i have. In row 1 i need conditional formatting such that if any of the columns contain an x the first row should be color coded. In my above example every column in row 1 will be color coded expect for row 1 column 6 since there are no x's for any any row in column 6.
How can i do this in excel.Also instead of x's if i have manually entered color can we do the same ?
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
Jul 18, 2006Is 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 RelatedConditional Formatting Based On Another Cells Value?
Aug 18, 2014On 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 RelatedConditional Formatting Based On Cells?
Mar 30, 2014I 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 RelatedExcel 2007 :: Conditional Formatting Empty Cells Based On Full Cells?
Nov 17, 2011Working 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.
Conditional Formatting - Based On Formula - If One Of 3 Criteria Are Met Then...
Jun 23, 2009I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.
View 2 Replies View RelatedCell Formula Based On Conditional Formatting?
Dec 3, 2012Say 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.
Conditional Formatting If Multiple Cells Contain Same Text
Dec 30, 2012I 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?
Conditional Formatting For Cells That Do Not Contain Text Range?
Mar 24, 2014I'm trying to create a conditional format that formats only cells that do not contain one of the names from a range of cells.
For example:
Cell O2 contains Florida
Cell O3 contains Texas
Cell range W1:W2
W1 = Florida
W2 = Arizona
In this example I want Texas to have a strikethrough, but Florida to be unchanged since Texas is not included in the list.
I keep getting an error when I try to do a format of text that does not contain =W1:W2.
Adding Cells Based On Conditional Formatting
Sep 17, 2009I 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.
Conditional Formatting Based On Sum Of Pairs Of Cells
Nov 14, 2013I 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.
Conditional Formatting Based On Another Cells Contents?
Sep 19, 2012I'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.
Conditional Formatting - Based On Another Cells Contents
Apr 27, 2007I 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 Formula Based On Rows Equaling Each Other
Jan 15, 2014I have two columns
Column L and Column W
When I input something into Column W, I want it to turn green if it is equal to the corresponding value in Column L
So say we're on Row 50
And I put $280 in Cell W50
I want it to turn green if W50=L50
And red if W50 does not equal L 50
Infinitely in the same column
Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?
Oct 2, 2013I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.
CELLS BK10:BN10 are merged and have entered into them the following text "SELL"
Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"
=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))
Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%
When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?
I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.
Conditional Formatting For Entire Row Based On Presence Of Any Text / Values In Column
Oct 19, 2013I 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.
Possible To Use Conditional Formatting To Recognize Or Ignore Certain Text In Cells?
Mar 2, 2014I am working on the monthly duty roster that is attached. I have the cells at the bottom configured to total how many early shifts, late shifts and off days each Trooper has. My problem is that I often need to add text to show what type of leave is being taken, or numbers to correspond to the special notes section numbered 1-24, and so on.
Is there any way to use conditional formatting to do this so that the totals at the bottom still sum correctly? Badge numbers are used to identify each Trooper. I have included them at the top for reference. The reason that I referenced them to total by cell instead of individual badge numbers is that I am attempting to format this so that any badge numbers can be entered at the top. This form will be used by other groups of Troopers if I can get it to work.