Conditional Formation More Then 3 Conditions VBA
May 1, 2009
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 9 Replies
ADVERTISEMENT
May 4, 2009
I have a field where the month is written as text. I would like to highlight row whose month column has passed. My conditional formating code is:
View 4 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Jul 24, 2007
I am trying to create a floating bridge bar chart.
Here is my data and I will try to describe as accurately as I can.
FY 2004 operating margin = 14.6% (while FY 2005 and FY 2006 = 11.2% => So the difference is 340bps)
The "bridge" back to 14.6% = 90 basis points for Jimmy'Z, 30 basis points for stock option expense, 30 bps for marketing -- that's 150 bps of the 340 and I'm trying to quantify the rest of the bridge but is there a way to show this in excel with floating bars - that is to say the 14.6% bar will touch the axis but the other 3 data points are not touching the X axis but are in the air in a step formation... ?
View 4 Replies
View Related
Jun 9, 2014
In the attached spreadsheet, the numbers in col A derived from another spreadsheet. The table D1:L21 is precaculated based on projection. I need 2 cells highlighted when they capture the number from the same row (col A). In another word, whenever the number from col A fall in between the 2 cells in the same row, they will be highlighted. Another condition is when it is smaller then 10 reps, the corresding cell in col D will be highlighted, as demonstrated in the attached table. I tried AND(E2<A2, F2>A2) but it didn't work. I also tried LARGE((E2:L2<A2)*(E2:L2),1) and similar SMALL function. They work as an array in normal excel, but not in the conditional formatting.
View 5 Replies
View Related
Jun 27, 2013
I am trying to fill a cell green if the current value > avg value and in red if < than avg value. I have tried to use conditional formatting but I cannot see where i can add additional rules. In any case here is what I am trying to do:
Daily Avg. Units:42
Avg Bgt. Units:7
The cell containing the number 42 is filled in green. If the figure was less than 7 it would have been red.
View 11 Replies
View Related
Mar 6, 2008
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 14 Replies
View Related
Jan 23, 2010
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 9 Replies
View Related
Jan 26, 2007
I have tried it, and it isn't changing the cells when I test the macro. I'm obviously doing something wrong, but can't figure out what....
View 9 Replies
View Related
Jun 4, 2008
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 9 Replies
View Related
Oct 17, 2008
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 9 Replies
View Related
Jul 10, 2009
Is it possible to apply confitional formatting on 6 conditions ...
View 9 Replies
View Related
Jan 8, 2010
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 9 Replies
View Related
Oct 31, 2006
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 5 Replies
View Related
Jan 26, 2007
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 2 Replies
View Related
Apr 2, 2007
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 3 Replies
View Related
Aug 10, 2007
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 8 Replies
View Related
Nov 6, 2008
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 2 Replies
View Related
Oct 11, 2013
I am trying to do some conditional formatting to highlight rows based on several conditions, I don't know if this is possible, but these are the criteria:
AAPL
tt
10/1/2013 11:14:15
GOOG
ll
10/1/2013 11:14:20
[code].....
I want to highlight a row if:
- the difference in time value is < 20 seconds
- the first symbol column is the same
- at least one, but not all of the rows meeting the above criteria contains a value in the second column from a given list (in this example, say tt is on this list)
So if done properly the logic would highlight the first and third rows, but would highlight none if none of the values in column two match my list.
I think the test criteria would go something like this, however I know that there are problems with my row references.
=and(abs($C2-$C3:$C65536)<0.0003,$A2=$A3,match($A2,Sheet1!A:A,0)>0)
View 2 Replies
View Related
Apr 23, 2009
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 3 Replies
View Related
Jun 10, 2009
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 14 Replies
View Related
Oct 2, 2009
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 3 Replies
View Related
May 14, 2012
I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.
I need to highlight the row if column W has a
G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)
how I can do this?
View 7 Replies
View Related
Oct 21, 2013
So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]
My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony
[Code] ....
As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.
View 3 Replies
View Related
Apr 15, 2009
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 9 Replies
View Related
Sep 21, 2009
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 9 Replies
View Related
Apr 24, 2002
Can I have more than 3 conditions for the conditional formatting function?
View 9 Replies
View Related
Nov 14, 2006
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 6 Replies
View Related
Jan 23, 2009
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 7 Replies
View Related
Jan 23, 2008
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 9 Replies
View Related