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 Complete Thread with Replies
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.
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.
RAG Formatting With Mutliple Conditions
I want to be get a response of Red, Amber or Green depending on:
A1 = blank and;
A2 = blank and;
A3 (which is a date) = greater than or equal to todays date
A1 = blank and;
A2 = blank and;
A3 (which is a date) = todays date plus 1.
A1 = blank and;
A2 = blank and;
A3 (which is a date) = greater than todays date plus 1.
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.
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.
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.
Case <30 red row
Case 31-60 orange row
Case 61-90 Yellow row
Case 91-120 Green Row
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 ?
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
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
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"
B)The date in the corresponding row from column C is more than 15 days old.
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?)
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)?
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:
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"
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.
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
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
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 .......
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.
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.
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]
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).
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).
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?
Color Cells Based On Multiple Conditions Of Different Cells
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
Copying Cells Under Certain Conditions Using VBA
I have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
Format Cells On Conditions
I have a Macro that when run checks for the lowest value in a line & highlights that line. It works just as it should, infact it works better than I had planded. When I update a cell value and move to the next cell it changes the highlight.
Selection.FormatConditions.Add Type:=xlExpression, _
Selection.FormatConditions(1).Interior.ColorIndex = 6
Cells(1, 1) = "Updated " & Now
Color Cells By More Than 3 Conditions
I am getting a "Run-time error:'13' Type Mismatch" when we try to paste a value (in this case "Y" or "N") in multiple cells at once. I did look up this error on the microsoft site and found this. http://support.microsoft.com/kb/821292. which didn't seem to be the exact issue I was having because I am not using a macro.
Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
icolor = 6
Case "n/a", "N/A"
icolor = 13................
Count And Sum Cells Meeting Two Conditions
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.
I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.
Next, I fill in each day with an employee name.
Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:
Friday 4 (total number of days jones is in a blue box)
Saturday 5 (...on a green box...and so on...)
For each employee name. Sounds easy, right? I can't get it to work!
Counting Cells With Multiple Conditions
I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.
I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.
Populating Cells When There Are Duplicate Conditions
if the state, city and company name are the same, I need to make sure the address gets populated in the address column. I have included an attached sheet to clear this up.
Row 1 is Crazy Horse Saloon in Anchorage, AK and has an address and phone number
Row 2 is Crazy Horse Saloon in Anchorage, AK and does not have an address and phone number
I need it to be able to realize that the State, City, and Business all match and populate the missing information.
Mark Cells Where 2 Column Conditions Are Met
I have a macro that's supposed to update the value in column 1 when two conditions are met. Unfortunately it doesn't update the column at all:
Dim item, rownum, maxrownum As Integer
Application. ScreenUpdating = False
maxrownum = Range("A2000").End(xlUp).Row
For rownum = 2 To maxrownum
If (Cells(rownum, 1).Value = "=") & (Cells(rownum, 29).Value = "OPEN") Then
With Cells(rownum, 1)
.Value = "APPROVED TRIAL"
I took the code from another spreadsheet I have where it works fine. But there only one condition has to be met before the field is updated.
Average Of Last X Cells With Conditions/Criteria
I have an excel sheet with different data columns (Column A = date, Column B = Production line, column C = total production of the line for the month, column D = complaints per milion). Within the same sheet Columns E through L have formulas and fucntions that uses Columns A thru D for calculation. Also every month a new row is added to the sheet, populating of course Columns, A, B, C and D and the other columns E thru L are populated automaticaly with the functions/formulas i have in place.
My question is within the same sheet I have 4 fixed cells I2, J2, K2 and J1. J2 and K2 depend on I2 and K2 values. Since my date changes every month (the inserion of new rows). I would like if someone can help me in how those cells I2 and K2 can be updated when i enter a value in Column C/ cell?? (??=next blank cell). I2 and K2 calucalates the averages of the last new 17 cells of columns H and L respectively.
So all i need is that cell I2 and K2 be updated automatically as soon as i add the new value in column C Example
C22 = 12345 I2=Average(H5:H22) and K2=Average(L5:L22)
C23=78901 I2=Average(H6:H23) and K2=Average(L6:L23)
So I type the input in column C everything is updated automatically. Again i dont know if i can do that with the if/offset, etc or if i need a macro.
Count Cells Using Multiple Conditions
I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.
I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes
Merging Table Cells With Text Under Certain Conditions
From a supplier I will get a spreadsheet including the the following Information which I need to convert into an url including trailing slash (/) for an online store.
Column A = Value of Main Cartegory (e.g. Holidays)
Column B = Value of Subcategory_1 (e.g. Christmas Decor)
Column C = Value of Subcategory_2 (e.g. Candle Holders)
Those raw data are without any trailing slashes.
Now I want to create a column e.g. Column D (product_url) which should be a summary of column A thru C but if there is only a Value for Main Category
there should be no trailing slash,
if there is a Value for Main Category and a Value for Subcategory_1: no trailing slash after Subcategory_1 but trailing slash between Main Category and Subcategory_1.
The result should look like this:
Holidays (Main category)
Holidays/Christmas Decor (Main category & Subcategory_1)
Holidays/Christmas Decor/Candleholders (Main category & Subcategory_1 & Subcategory_2)
Is it possible to create something like that in Excel at all since all values will only be text and the trailing slash is actually functional for Excel calculations but in this case it would have to be treated as "text".
Code To Recalculate Certain Cells Only Meeting Certain Conditions
I have two sheets. Watchbill and Roster. The Roster contains a list of names that are randomly generated to the watchbill sheet into more than 60 seperate cells. My problem is the names are not being distributed fairly. If I had 20 names and 20 cells, it only uses say 12 of them and repeats 8 names, thus leaving 8 persons out of the generation completely. I did not know how to fix this so I used a system to identify when a name was repeating in the series of cells by displaying Repeat in the cell beneath it. I then had the names that were not placed on the watchbill (the other 8 not used) highlight themselves in blue on the roster sheet so I could manually insert them on a third sheet into a cell set to override the repeating name on the watchbill. This was my back door system to solve my repeating names problem. However this takes forever. I want some vba that will identify the cell with the "repeat" underneath it and automatically recalculate the cell until it doesn't repeat. I only want the cell recalculated, not the sheet. This code would have to do this for about 70 cells making sure no names repeat at all, unless there isn't enough personnel to fill the positions, then it would allow the names to repeat. Can that be done and what would the code be?
Concatenate Cells Or Ranges Based On Conditions
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
Color Cells Within Matrix Based On Conditions Outside
In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.
Each factory has its own worksheet as well.
In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.
I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.
This is the formula:
However, this simply checks that data while there is only 1 quantity for each row of each house.
Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,
If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.
Check Conditions In Multiple Cells With One Cell
=AND($AE$2:$AE$10<=$F$2:$F$2000;$AE$2:$AE$10>=$E$2:$E$2000) With this I am checking for conditions to be true or false
Lets say I am checking AND(AE2 <= F2 ; AE2>=E2) this tells me if date in cell
AE2 is smaller or equal to Cell F2, but how can I set this fromula to check if all the cells from F2 to F1000 are equal to AE2 so If I write date into Cell AE2 it checks all dates from F2 to F1000 and check if condition is true or false
(and if I write date in cell AE3 it should check all the dates from F2:F1000, if date in AE3 is in this range)
Compare Cells For Conditions & Color Corresponding Cell
I am trying to add a third if statement to my existing code looking to see if a cell in a column is blank and the corresponding cell in another column contains a number that is greater than or equal to 1. I can't figure out how to write that part of my IF statement.
If Cells(r, 4) = " " And Cells(r, 3) >= 1 Then
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 35
Set rng = Union(Cells(r, 1), Cells(r, 3), Cells(r, 4))
Worksheets("Sheet1").Range("G40:I40").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Cells(r, 5) = "NEW"
Copy Cells Meeting Conditions Or Criteria
We are trying to list only certain cells that have a specific conditional format- this is so we can copy them to another sheet
A filter does not really work for us since we have over 5 columns to search on and over 800 records
This is how excel says to find cells that have a conditional formatting (for example the scores are red because the student scored between 350 and 370: I tried it and it doesn't work--but maybe that is not how it is suppose to work?
To find cells that have conditional formatting settings identical to the settings of a specific cell, click the specific cell.
On the Edit menu, click Go To.
Click Conditional formats.
Do one of the following:
To find cells with any conditional formatting, click All below Data validation.
To find cells with identical conditional formats, click Same below Data validation.