How To Add More Conditional Formatting Conditions Than 3?
Why is there a hard limitation like this? Is there a way to go around it? By having a different format for the cells outside the 3 conditions I can get 4 different formats altogether, but that's not really that much.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Conditional Formatting With 4 Conditions
I am using a drop down list in excel and want to conditional format the cells based on what is selected from the list. I can't use conditional formatting as I have more than 3 items in the list. I am guessing VBA can help but am a complete novice so could use some handholding on what to do. Idea is that if someone select "Good" from dropdown, cell becomes green, "Stable" is amber and so on.
View Replies!
View Related
Conditional Formatting With Four Conditions
I want to use conditional formatting for RAG reporting. I had done this by having Cell is R then the format would be RED, Cell is A, then yellow, etc... However I would like to have a blank cell or N/a no format the cell at all-currently this is making the cells green. Do I need to use Formula instead? and tips on how this could be written would be great as well.
View Replies!
View Related
Conditional Formatting - More Than 3 Conditions
I'm trying to add conditional formatting to a column of data but there are more than 3 conditions. Is there a way of increasing the number of conditions you are able to set using the conditional formatting wizard or, if not, is there a way of applying conditional formatting using VBA ?
View Replies!
View Related
Conditional Formatting More Than 3 Conditions
I am running Excel 2003 and when I open the file, I get the error: Invalid procedure call or library. When I check the references, it has Microsoft Excel 11.0 object library Microsoft Office 11.0 object library and I manually added Microsoft Outlook 11.0 object library Not only do I get the same error, but when I try to save, it just gives me the totally unhelpful error message: File not saved
View Replies!
View Related
Conditional Formatting: More Than 3 Conditions
I am trying to colour cells depending on the text in the cell. The text includes letters and symbols (e.g. A+ or C). The problem is I have five different conditions and Excel will only let me set up three. The conditions I require are: Text: A+ or A Colour: Pink Text: A- or B+ Colour: Light Orange Text: B or B- Colour: Sea Green Text: C+ or C Colour: Lavender Text: C- or D Colour: Brown
View Replies!
View Related
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 - With Two Conditions Within One Formula
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
View Replies!
View Related
Conditional Formatting: Greater Than 3 Conditions
I have a cell that I want changed to green (red 133 Green 180 Blue 0) if a line is considered active. As conditional formatting only allows 3 arguments, I need to perhaps code it? Along the columns are cells that can change a row to active or inactive. Here are my arguments. If B5>1 .. row = active If B5>1 & G5>1 .. row = inactive If B5>1 & G5>1 & H5>1 .. row = active If at any time L5>1 & M5="" .. row = inactive .. OR If at any time Q5>1 & R5="" .. row = inactive .. BUT If at any time L5>1 & M5>1 .. or .. Q5>1 & R5>1 .. row = active then .. If H5>1, M5>1, R5>1, T5>1 & AH5="", AJ5="", AL5="" .. row = active so If at any time AH5>1 .. or .. AJ5>1 .. or .. AL5>1 .. row = inactive
View Replies!
View Related
Conditional Formatting For 4 Conditions In Vba
Need to figure out a code for Conditional formatting to work for 4 cases. I dropped programming 5 yrs ago...have forgotten mostly everything but I know this can be done. What I need: Range = entire sheet Upon entering a date(X) anywhere in the row the date is calculated from today's date(Y) and if the date entered is -infinity to 30 then the entire row turns red. 31 to 60 days orange, 61 to 90 days yellow, 91 to 120 days green, 120+ nothing. Basically Y-X. Case <30 red row Case 31-60 orange row Case 61-90 Yellow row Case 91-120 Green Row 121+ nothing.
View Replies!
View Related
Conditional Formatting That References Two Separate Conditions
I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is: 1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged. 2) Aging depends on the "priority" status in column A .......
View Replies!
View Related
Multi Conditions Formating Without Conditional Formatting
I have a spreadsheet with data in columns A:K. I want to be able to change font color for a row based on what is found in column J and K. In other words if J3 contains the letter M then row 3 should be Green. I know how to do this using conditional formating, however I have 2 problems. First, I have 6 different conditions to meet. Second, there is one more condition to meet. If the date in column K3 is older than todays date then row 3 should be turn Red regardless of what is found in column J3. jamm
View Replies!
View Related
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: Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1] 2. Turn the cell background colour orange (action required) if: Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View Replies!
View Related
VBA Conditional Formatting To Highlight Certain Conditions
I have a spreadrsheet (sample attached) which populates a calender due to columns on seperate sheets being filled with text and corresponding dates. I started using conditional formatting to highlight certain conditions but soon realised I need more than 3 so I tried looking into VBA - and failed. All the cells in the calender have the 3 usual conditions associated to them but in addition I need to be able to colour individual cells if they contain certain phrases or parts of phrases like "BP" or "Current"
View Replies!
View Related
How Can I Use Conditional Formatting To Mark Up This Set Of Error Conditions?
I have a lot of data (regarding graffiti) in a table and all my formulas work well. But, because I sometimes make an error in two columns of data I sometimes get results that do not balance. I want to put in a conditional format (?Is there a better way?) that will highlight the error as I enter the data. Can you help please? Using Excel 2003 (sp3) and no add ons. There are lots of identical rows, 5:400, making up a table. I will use row 10 as an example. In column M there is a formula which may or may not give a visible result. (It's a measure of the number of days taken to clean the graffiti and will only hold a result if the job has been finished.) The result for "M10" will be a number between 0 and 5 90% of the time, but may creep up to 30. It may also leave the cell looking blank although the formula itself is still lurking out of sight. A matching row of cells, O10:T10, have no formulas in them. I must manually enter a "w" or an "a" into ONE of these columns, depending on the graffiti location (Worthing or Adur) and property ownership (Columns for residential, commercial, council etc.) So now the errors I need to flag up as I enter data are to ensure that M and O:T balance. There should always be a result in M if there is an entry somewhere in O:T and if there is an entry in M there must be an entry somewhere in O:T. Sometimes I mistakenly put an entry in O:T even if there was no result in M. Sometimes there is a result in M and I fail to put an entry in O:T. I need to be alerted as this happens, if possible. It will save hours of trying to track down my data entry errors. BUT, there is already one conditional format already in M. It highlights the cell if it goes over 5 days for cleaning. =ISNUMBER(M10)*(M10>5). If you can help, thank you very much. I thank you, my wife thanks you for me not doing overtime trying to track down my errors, my boss's thank you for making me more efficient and other forum readers may thank you for helping them to improve their sheets.
View Replies!
View Related
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).
View Replies!
View Related
Remove Conditional Formatting Conditions, But Retain Formats
I am using excel 2007 - I need the extra available columns in Excel 2007 for my application. I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated. Format painter and "paste special - format" copies any formats as well as the conditional format rules. Is htere a method within Excel to do this or can someone suggest a workaround?
View Replies!
View Related
Conditional Formatting / Add Duplicates
I need to create a formula that will look at a p/n find its match and add the value from all matching p/n together. After this is done, all of the line items that matched I need them to turn red. But I do not want the first p/n to turn red. It is better explained in my example.
View Replies!
View Related
Add Conditional Formatting Criteria With Code
I recorded some code and cleaned it up to apply a conditional format Sub condi_format_I() 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") With rng .FormatConditions.Delete .FormatConditions.Add xlExpression, Formula1:="=C4<0" .FormatConditions(1).Interior.ColorIndex = 45 End With End Sub 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
View Replies!
View Related
Add A Single Expression For Conditional Formatting Across All Cells
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows) To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression. I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases. I have built the following expression which does not seem to work. =ISNUMBER(SEARCH("Yes",(ADDRESS(ROW(),COLUMN())))) The two components of the expression work separately: =ISNUMBER(SEARCH("Yes",P3)) =ADDRESS(ROW()-1,COLUMN())
View Replies!
View Related
Macro To Add Conditional Formatting To Variable Range
I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following Sub CellCCondFormatting() Dim j As Long Range("C2").Select j = Range("C2"). CurrentRegion.Rows.Count MsgBox ("1st: " & j) Range("C2:C" & j).FormatConditions.Delete Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=D" & j Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3 Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=D" & j Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4 MsgBox ("2nd pass: " & j) End Sub It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................
View Replies!
View Related
Macro To Add Conditional Formatting Code To Variable Range
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
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
Multiple Conditions For Formatting
conditional formatting with multiple criteria needs. I don't even know if it is possible but thought it just may be possible using VBA or some formula in conditional formatting. Please take a look at the sheet and let me know if it makes sense or if you need any further info.
View Replies!
View Related
VB Color Formatting More Than 4 Cell Conditions
The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work. In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)
View Replies!
View Related
RAG Formatting With Mutliple Conditions
I want to be get a response of Red, Amber or Green depending on: Red if A1 = blank and; A2 = blank and; A3 (which is a date) = greater than or equal to todays date Amber if A1 = blank and; A2 = blank and; A3 (which is a date) = todays date plus 1. Green if A1 = blank and; A2 = blank and; A3 (which is a date) = greater than todays date plus 1.
View Replies!
View Related
Number Formatting With Multiple Conditions
Problem : Belgium has three types of phone numbers ##/###.##.## (if prefix is "02" or "03") ####/##.##.## (if prefix is "04##") ###/##.##.## (everything else) depending on the prefix. How can I put a conditional number formatting on a cell, so that it puts the input (eg. 025648595) in the correct format (in this case : 02/564.85.95, or something along these lines)?
View Replies!
View Related
More Than 3 Formatting Conditions - Select Case
I have 5 formatting conditions that I need to add in my macro and have just recently found out that I can't have more than 3 in my version of Excel - DOH. Anyway, some research has indicated that you can use something called select case to get round this? I need to colour the range as follows:
View Replies!
View Related
VBA Formatting Cells With More Than 3 Conditions
I have a spreadsheet which I need more than 3 conditions to the formatting - is there anyway of doing this in VBA!!! Its for a holiday chart that i'm doing - some of the items they can enter into one cell is: B (Bank Holiday) / 1 (1 days holiday) / TR ( Training day) / SL (Study Leave) / 0.5 (half day hols) and more.
View Replies!
View Related
Conditional Formating For 6 Conditions
I have a spreadsheet B4:B193 - place D4:D193 - value F4:F193 - name H4:H193 - place J4:J193 - value L4:L193 - name N9:N93 - place P9:P93 - value Q9:Q93 - name What i am looking for is this: If value in cell in column D is >=90 then relevant cell in column B, D and F background green If value in cell in column D is less =80 then relevant cell in column B, D, and F background dark blue If value in cell in column D is less =70 then relevant cell in column B, D, and F background light blue If value in cell in column D is less =50 then relevant cell in column B, D, and F background orange If value in cell in column D is less
View Replies!
View Related
Formatting Cell Colors With Multiple Conditions
so what will happen is that the number of rows will continuously increase as the groups of data (Labeled Group #) increases. The data within each group will vary from time to time so some groups may contain only one line while others may contain multiple lines. I have included an attachment that illustrates what I'm trying to achieve. When data is entered in Column A under "Q," the adjacent empty cells in Columns B-F (labeled "R"-"V") will turn red. This is the part I have (i.e. Group 2).
View Replies!
View Related
More Than Three Colour Conditions In Conditional Format
We have 6 words we need to add to a sheet, each cell needs to be a different colour on the basis of the word. I was hoping to use CF as the sheet needs to be locked against people editng cells and the like. My question is, could a conditional format say if "service" colour blue, if "parc" colour green. If this is possible this would allow me to use 6 different colours.
View Replies!
View Related
Pasting Conditional Formats Without The Conditions
I have a spreadsheet containing all sorts of formulas & data in Columns A - H, and a formula copied down in Column I generates a number that drives a few conditional formats in the preceeding columns. I'm trying to copy-paste the values and formats of Columns A - H into a new workbook with this pretty simple macro... Range("A1:H195").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False When the formats are pasted across it's pasting the conditional formatting, but they're not displaying correctly because Column I isn't coming over to the new workbook. Is it possible to copy-paste the cell formats as they stand regardless of whether there's any conditional formatting behind it?
View Replies!
View Related
Combining Conditions Within Conditional Formula
i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need. Basically i need to say the following: =if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
View Replies!
View Related
Conditional Formation More Then 3 Conditions VBA
I have some data in cells C5:G50 I can then enter some data in cells C4, D4, E4, F4, G4 What i want now to happen is when the value in C4 matches a value anywhere in C5:G50 then i want that cell to have green background etc. I have found this code but it does not seem to work Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("C5:G50")) Is Nothing Then For Each c In Intersect(Target, Range("C5:G50")) Select Case c Case Is = "C4" icolor = 5 Case Is = "D4" icolor = 5 Case Is = "E4" icolor = 5 Case Is = "F4" icolor = 5 Case Is = "G4" icolor = 5 Case Else icolor = xlNone End Select Intersect(Rows(c.Row), Range("C:C,D:D,E:E,F:F,G:G")).Interior.ColorIndex = icolor Next c End If End Sub
View Replies!
View Related
Add Sheets Based On Conditions
i am not able to solve this problem. 1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table 2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month. 3- Reference table has the list of the same data record ( numbers ) . Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following. a- Check for the record in the reference table , if doesnt exist then add the record in the reference table b- create another sheet same as the sheet " A" (duplicate of A) and name it the new record number b- Plug the new record number in the cell B2 of the new sheet c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to O8)from sheet A
View Replies!
View Related
How To Add Range From Multiple Sheets With Conditions
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter. =SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04")) gives me a #NAME? error. Example Data on each spreadsheet: A B SU 004*02 SU 004*04 SU 004*04 SU 004*02 SU 004*04
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Multiple Conditional Format Conditions Changed Based On Another Cells Value?
A1:A6 have numeric values 1 to 6. I want the conditional format values to change Based on the value in Cell B2. If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text. IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text. I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View Replies!
View Related
Convert Imported Dates & Add Periods Based On Conditions
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd I desire to look in column "E" and I have 3 different variables. "J", "K", "L". If column "E" has a j add 30 to the years; K add 35 years and L add 38 years. for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
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
|
TRACKER
