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 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
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"
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.
VBA For Conditional Formatting Of Cells With Different Month Values
I have two date columns, say E & F. In these two columns I have entered date values. I need to do colour, font bold, Italic automatically when date values are entered in these columns - with a conditional formatting - i.e. every month should be in different color.
For example, values 23/05/2009, 15/10/2005, 07/12/2004 must be automatically coloured with different colors as its months are different.
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.
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 - 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?)
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.
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:
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 .......
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).
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.
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.
VBA Multiple IF Conditions
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
I am looking to do something similar on excel 07 as follows:
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
Conditions On VBA Event
Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my
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
icolor = xlNone
Intersect(Rows(c.Row), Range("C:C,D:D,E:E,F:F,G:G")).Interior.ColorIndex = icolor
VBA Do While Loop With Multiple Conditions
I'm having some problems getting a simple Do While loop to run. Essentially I'm trying to get it to run as long as one of two conditions is met, so I used an Or function in the loop condition. This then produces the "Run time error 1004: Application defined or Object defined error" error on that line.
I'm certain my syntax for the two conditions is correct (one is a simple check value in a cell, the other is check the value of a VBA variable) so it would appear the error is caused by the inclusion of the Or function. So I guess my question is this, have I done something wrong? If so what and if possible why is what I did wrong (I like to understand these things)? Is this something that has to be done in a different way or even is it something that cannot be done? I'm using excel 2003 on XP SP3.
VBA Code. Check Multiple Conditions
I want to create a user form in excel that auto calcs the discount depending on customer type and no. of books purchased. There are two types of customer’s one individual and the other schools, library etc. The discounts are below
More than 50 books - 30%
25 - 49 - 20%
15 - 24 - 15%
5 - 14 - 10%
<5 - 5%
>25 - 25%
5 - 24 - 15%
<5 - 0%
I have created a form with option and text boxes to capture data but am really struggling with this as in my head I understand a set calculation will need to take place once relevant conditions are met but am really struggling to put it into code (yes I am new to this).
Vba To Delete Rows Based On Certain Conditions In Column A
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
Using VBA To Select A Row And Colour Code It Based On Conditions
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something!
I have a spreadsheet where column O has five conditions:
1 PO in
2 High Probability
3 Medium Probability
4 Low Probability
I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA.
However, I'm having difficulty colouring the whole row; how do I do this?
e.g. what I need to do is:
If O2 = "1 PO in"; Select A2:T2; Colour green
In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
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................
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.
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
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!