Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

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 4 Replies


ADVERTISEMENT

Get Conditional Formatting To Change The Cell Color Based On A Few Conditions?

Jul 19, 2013

I'm trying to get conditional formatting to change the cell color based on a few conditions.

I want the cells in column D to turn red if;

- The number in column B has a match in column A

- And the corresponding cell in column C is not blank

- And the corresponding cell in column D is blank

In the example below, cell D4 should be red

If there's a match (Column B to Column A) and;

- If cell in column C is not blank and cell in column D is not blank, do nothing.

If there's a match (Column B to Column A) and:

- If cell in column C is blank and cell in column D is blank, do nothing.

Here's the formula I attempted but didn't get it to work.

IF(AND(C2"",D2=""),MATCH(B2,$A$2:$A$15,0),IF((AND(C2"",D"")),"",""))

View 3 Replies View Related

Excel 2010 :: VBA To Apply Cell Protection Based On Conditional Formatting Result

Jan 27, 2012

I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.

I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.

I am using Excel 2010 and Vista.

View 1 Replies View Related

Conditional Formatting Based On Several Conditions

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

Conditional Formatting More Than 3 Conditions Based On Dates

Feb 8, 2009

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 9 Replies View Related

Excel 2010 :: Conditional Formatting Based On Two Conditions

Mar 29, 2012

I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.

I am working in Excel 2010

View 1 Replies View Related

Conditional Formatting To Turn Field Specific Color Based On Two Conditions

Aug 9, 2014

I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.

I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.

See the attachment : Event Milestones based on Go Date Uploadable.xlsx‎

View 10 Replies View Related

Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

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

Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

View 3 Replies View Related

Get A Result Based On Several Different Conditions

May 20, 2009

New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.

The following is a table which shows a salesmans sales. He or she has sold to 8 customers.

What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.

1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get 40

2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get 25

3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...

View 6 Replies View Related

Conditional Formatting With 2 Or 3 Conditions?

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

Conditional Formatting With More Than 1 Conditions?

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

How To Add More Conditional Formatting Conditions Than 3?

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

Conditional Formatting With 6 Conditions?

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

More Than 3 Conditions For Conditional Formatting

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

Conditional Formatting With 4 Conditions

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

Conditional Formatting More Then 3 Conditions

Jul 10, 2009

Is it possible to apply confitional formatting on 6 conditions ...

View 9 Replies View Related

Conditional Formatting With Four Conditions

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

Conditional Formatting: More Than 3 Conditions

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

Conditional Formatting For 4 Conditions In Vba

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

Conditional Formatting - More Than 3 Conditions

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

Conditional Formatting More Than 3 Conditions

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

Evaluate Conditional Formatting Result

Sep 28, 2007

I'm having trouble figuring out how to evaluate the result of Conditional Formatting using VBA.

I've got a series of cells ( dates) that are formatted in BOLD when a corresponding cell elsewhere in my spreadsheet shows that the date is Actual and not Projected ("A" vs. "P").

The Conditional Formatting works fine, I just can't figure out how to get VBA to skip the cell if the value is not BOLD based on the Conditional Formatting result.

ex:

For Each i In Selection
If i.FormatConditions(1).Formula1 = True Then
'OR i.FormatConditions(1).Font.Bold = True Then
i.Copy
i.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
End If
Next i

View 6 Replies View Related

Conditional Formatting - With Two Conditions Within One Formula

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

Conditional Formatting With Multiple Conditions

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

VBA Conditional Formatting To Highlight Certain Conditions

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

More Than 3 Conditions For The Conditional Formatting Function?

Apr 24, 2002

Can I have more than 3 conditions for the conditional formatting function?

View 9 Replies View Related

Conditional Formatting: Greater Than 3 Conditions

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

How Can I Use Conditional Formatting To Mark Up This Set Of Error Conditions?

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

Multi Conditions Formating Without Conditional Formatting

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







Copyrights 2005-15 www.BigResource.com, All rights reserved