Multiple Conditional Formatting On Text
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 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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Formatting With Multiple Conditions
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.
View Replies!
View Related
Conditional Formatting For Multiple Rows
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 Replies!
View Related
Conditional Formatting Text
I 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.
View Replies!
View Related
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?
View Replies!
View Related
Conditional Formatting Specific Text Containing ONLY
I have a conditional formatting rule that checks for misspellings of client names. My employees input client names into a spreadsheet, and I need for the spreadsheet to notify them via a fill color if they have misspelled the name. So, I set up a rule for each client name using the specific text criteria. The rule simply checks for the client's name, and fills the cell green if the client's name is correct. If the client's name is misspelled, then the cell remains unfilled. This works great if the client's name is spelled correctly, or spelled incorrectly. But there's one hitch: If the client's name is spelled correctly, but there is some extra character(s) inputted after the client's name, the cell is still filled green even though the data in the cell is useless because the extra character will cause it to be rejected by the next party down the line in the value chain. So I'm hoping to find a way to make the specific text containing criteria operate with only the specific text I specify and no extra characters or anything after the specific text.
View Replies!
View Related
Conditional Formatting, Variable Text
I can't figure out how to set up conditional formatting to let me know if the value of a text cell is NOT X or Y. In other words, the only appropriate responses are X or Y (or Y or N or whatever the case may be) and if it's NOT one of those two letters, I want to apply conditional formatting. Also, will it accept either upper or lower case (I'm fine with it either way).
View Replies!
View Related
Using Conditional Formatting To Hide Text
I have an IF statement which sometimes returns the word "FALSE" and a SUMIF statement which sometimes returns the error #DIV/0! I need to be able to hide the text in the cells when either value is returned. I've managed to solve one of them (#DIV/0!) by using conditional formatting and using the formula =ISERROR(H35) then changing the text colour to white. I can't think of a soution for the "FALSE"
View Replies!
View Related
Conditional Formatting For Cells Which Have Multiple Date Info
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...)
View Replies!
View Related
Conditional Formatting - Finding Text Color
I have a workbook with 4 sheets. Each sheet is set up the same. There are 4 columns. The column headings are Title, Item #, 2009 Retail Price, and Price changed from 2008 (in A, B, C and D respectfully). I need to find all red text in column C and place a 'Y' in column D. If the text in column C is not red I want a 'N' in column D.
View Replies!
View Related
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: [url] 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: ...
View Replies!
View Related
Check 10 Different Text Values And Return False For All Of Those In The Conditional Formatting Rule
Allows me to check like 10 different text values and return false for all of those in the conditional formatting rule. When those are not present, the conditional format is true thus applying the format. I tried =NOT(FIND("text1",$A$1,1)) It did not work because I believe this statement doesnt give the rule its TRUE value its looking for. I am having trouble with this, to clarify I need the below... Cell has conditional formatting checking for text1 text2 text3...text 10ish and if they are present nothing will happen and if they arent present then format the cell. EDIT: to clarify, the code should look for either text1 or text2 or text3 so on...
View Replies!
View Related
Locking Conditional Formatting In 2000: Copy/paste Text From Other Cells Or Columns Even Other Workbooks
I 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.
View Replies!
View Related
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: if(A1=blue background,"Yes","No") or if(A1=red text,"Yes","No") 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.
View Replies!
View Related
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?
View Replies!
View Related
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?
View Replies!
View Related
If Then Or Conditional Formatting??
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
View Replies!
View Related
Conditional Formatting
I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red<100% e.g. Is it possible to have a third conditional format that gives a blank if the cell is ""? At the moment it just turns out green as it doesn’t seem to recognise the cell as actually been blank as it has a formula within it.I have tried ISBLANK etc.
View Replies!
View Related
Conditional Formatting > 3
Conditional Formatting > 3 Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim cr As Date cr = Today() If Not Intersect(Target, Range("C5:M44")) Is Nothing Then Select Case Target Case "X" icolor = 10 Case "N/A" icolor = 2 Case cr.Value >= Today() icolor = 6 Case cr.Value <= Today() icolor = 3 Case "DATE?" icolor = 8 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub
View Replies!
View Related
Conditional Formatting Macro
I am looking to create a macro that conditionally formats a cell depening on what words it contains. if the cell has the word "level 1" in it = pink backround "level 2" = red backround "level 3" = Orange backround "level 4" = Green Backround "level 5" = Blue back round i would like this to run on the active cell.
View Replies!
View Related
Conditional Formatting With IF/Then Statements
I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on. The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46
View Replies!
View Related
Conditional Formatting With 6 Conditions?
I'm using Excel 2000 which has a limit of three conditions, I have 6. If you could just get me started, I still don't understand VBA enough to do this. In cell A1, the color is set to red. I want A1 to turn green when all 6 conditions are met: 1. Cell B1 has a valid value from its pull down list, no other value, and not empty. 2. Cell B2 has an integer, no decimal places allowed, and not empty. 3. Cell B3 has a valid value from its pull down list, no other value, and not empty 4. Cell B4 is not empty. 5. Cell B5 has a three decimal place number greater than zero. 6. Cell B6 has a single letter from A-Z only.
View Replies!
View Related
Copying Conditional Formatting
Cell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
View Replies!
View Related
Formula Using Conditional Formatting
1. I have a range of random numbers within a column, i.e. A1:A9 2. A specific number will appear in a cell at the end of that column, i.e. A11 3. I need to see via conditional formatting which of the above cells add up to that number. I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.
View Replies!
View Related
Extreme Conditional Formatting
I've exhausted searching the forum for help on this - Any input is appreciated Using Excel 2007 , Range A1..M50 I'd like to color entire rows 1 through 50 based on what text label is inserted into any cell in Col A1..A50, Upto 10 labels possible ie. Ten colors needed. Example, Cell A1 = "Dog", color Red range A1..M1 Cell A1 = "Cat", color Blue range A1..M1 Cell A1 = "Bird", color Yellow range A1..M1 .. Cell A5 = "Dog", color Red range A5..M5 Cell A5 = "Cat", color Blue range A5..M5 etc
View Replies!
View Related
Conditional Formatting Limit
I have an excel-sheet with two worksheets one called "sheet1", this is where all data is stored, and "sheet2", where data from sheet1 can be evoked. I'm now looking for some code that get arounds the 3 conditions limit of conditional formatting. Most of the codes on internet, just do this but only when entered manually on the selected sheet. In my case all data is entered on sheet1 and are displayed by some formulas on sheet2. It is on sheet 2 where I would like the color change to happen. I found a piece of code that was said to do just that, but when testing it appears not to. It only updates the cell color when the concerning cell is clicke twice.
View Replies!
View Related
Conditional Formatting With More Than Three Values
Im using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover. Basically this is what i want, but i dont know how to translate this VB IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour. IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour. IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour
View Replies!
View Related
Can We Do OR Function In Conditional Formatting?
Is it possible to use OR function in Conditional Formatting? I want to do the following. If A1 = 1, A2 cell is filled in Red If A1 = 2, A2 cell is filled in Red If A1 = 3, A2 cell is filled in Green, If A1 = 4, A2 cell is filled in Green, I only managed to do the above for 3 conditions as Conditional Formatting only allows me to no more than 3.
View Replies!
View Related
Conditional Formatting Using A Macro ...
I am trying to work with conditional formatting using a macro. How it works is I select the range I want to format manually then I run this macro. It doesn't work When I check the conditional format the formula is wrong. Instead of referencing the correct cell in columns E & F is is referencing E65282 & F65282 What do I need to change? ...
View Replies!
View Related
|
TRACKER
