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?
ADVERTISEMENT
Excel 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.
Multiple Conditional Formatting On Text
Aug 7, 2006I 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 Excel’s 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.
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.
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 Multiple Cells
Jan 28, 2014I 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
Conditional Formatting In Multiple Cells
Oct 25, 2007I 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.
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 Multiple Number Values But IGNORING Text?
Sep 6, 2013I have a spread sheet where there are multiple rows of price quotes from different suppliers.
Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".
SupplierA
SupplierB
SupplierC
SupplierD
1.3
No Bid
2.3
No Bid
How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.
=G31=MAX($G$31:$J$31)
This does not seem to work, I don't know how to exclude the text values from the MAX function.
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.
Conditional Formatting Of Duplicates Across Multiple Cells
Feb 25, 2014I 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 RelatedApplying Conditional Formatting To Multiple Cells?
Apr 25, 2012If 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 RelatedConditional Formatting Of Cells In Multiple Sheets
Sep 6, 2013I 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?
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 View RelatedPossible 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.
Conditional Formatting For Cells Which Have Multiple Date Info
Feb 10, 2009I 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...)
Excel 2007 :: Conditional Formatting On Multiple Cells?
Apr 17, 2012I'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).
Copying Conditional Formatting Formulas To Multiple Cells?
Jul 9, 2013I 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.
Conditional Formatting For Blank Cells To Display Custom Text
Feb 17, 2012I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"
The blank fields get the light green fill, but no text; that is, the value displayed is still blank.
I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).
This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.
I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].
I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???
I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.
I also made sure the option [x] Show a zero in cells that have a zero value is checked.
With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.
And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.
Conditional Formatting To Change Colour Of Cells Dependent On Text Within Them
Sep 8, 2013How to colour cells depending on text in other cells, for example,
I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?
I have included an example below,
Do you use a "*FSM*" around the formula?
[URL] ...........
Strings In Text Cells Of Lookup Table To Inform Conditional Formatting
Jan 3, 2013I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank
2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet)
3) A string that exactly matches an item in the lookup table
4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses
=VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value.
If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
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 Live Calendar Conditional Formatting With Multiple Cells In Column
Jul 15, 2013I 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
Conditional Formatting - Colouring Percentage Of Multiple Cells Based On Date Range
Mar 12, 2014I 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‎
Locking Conditional Formatting In 2000: Copy/paste Text From Other Cells Or Columns Even Other Workbooks
Jul 27, 2009I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?
Sep 16, 2013A 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
Conditional Formatting Of Text?
Oct 26, 2011Basically, I have a Rule that says:
=$A1="Mitigated"
I want the same format if Column A says either "Mitigated" or "Accepted".
I can't create a new rule as I have to save the file in 2003 for other users and I've got another 2 rules for varoius other things.
I've tried crudely putting "Mitigated"OR"Accepted" and it dosen't work.
Conditional Formatting On Certain Text
Oct 1, 2007I am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.
I want to do this in VB but I don't know how!
Conditional Formatting Text
Sep 28, 2009I would like to conditional format a range of sells which contains numbers formatted as text. I would like if the sell contains the specific number (which is formatted as text) to be formatted in red. It works for one value but I have to list 15 values for which this condition to be done.
For example: if some of the cells in the worksheet contains one of the following values: 265987,265489,235698,2356987,2356987,2356987,235569,...ets - 15 conditions - to turns red.
Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?
Feb 9, 2009I 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