Conditional Formatting With Multiple If / Or Statements

Aug 2, 2012

I am having difficulties trying to get some conditional formatting done.

For the below i need to highlight the 2012 YTD Column based on the following conditions, and i cant get the formula correct

GReen = If 2012 YTD meets /beating both 2012 target and 2011 Actual figures =And(D2

View 1 Replies


ADVERTISEMENT

Can Conditional Formatting Formulas Have Multiple Statements

Aug 20, 2009

Can you have IF and AND statements in Conditional formatting formulas?

I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.

View 12 Replies View Related

Conditional Formatting With IF/Then Statements

Dec 1, 2008

I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on.

The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46

View 10 Replies View Related

Conditional Formatting With If Statements

Jun 12, 2007

I have a spreadsheet that has several columns containing names and data. In the first column, is a person's name, in the second, is a client number for that person, and in the third is the "last contact" date. I have a fourth "reference" column that has a conditional statement I was trying to use for formatting. For example, in A1 would be "John Doe," in B1 would be 987, and in C1 would be 06/10/07. The next row would have data for a different person. In column E I have my reference column.

If the last contact date is less than 4 days from the current date (which is in cell K2), the reference column (using an if formula) will produce the number 1. If it is 5 or 6 days from the current date, it produces the number 2, and if it is 7 or more, then it produces the number 3. Here is the exact formula:

=IF($C2+7<=$K$2, "1", IF($C2+5<=$K$2, "2", IF($C2+4>=$K$2, "3")))

What I want to happen is this: if the reference column produces a 1, then I want the corresponding row to turn red. This would only include the name, assigned number, and last contact date. I do not need the entire row to change a certain color. If a 2 is produced, I would like the row to turn yellow, and if a 3 is produced, I would like the row to turn green. This is going to run for many rows (up to 150). How do I do this using VBA? Keep in mind the numbers in the reference column will change if the "last contact date" is updated to a more current date.

View 6 Replies View Related

Multiple IF Statements (skip The Next Two Statements Or The Result Will Be Changed Again)

Nov 22, 2008

I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.

View 2 Replies View Related

Bank Statements - Formatting / Cell Formatting Won't Change?

Mar 22, 2013

I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.

Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:

Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel

Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.

For some reason nothing seems to work - what am I missing?

For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.

View 3 Replies View Related

Conditional Formatting Across Multiple Spreadsheets?

Jun 5, 2014

I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.

I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.

There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.

View 4 Replies View Related

Conditional Formatting For Multiple Rows

Jul 8, 2009

I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.

View 4 Replies View Related

Conditional Formatting Over Multiple Pages

Dec 17, 2012

I need setting up a conditional rule over 2 pages.

Page 1 displays a list of countries.

Page 2 pulls in the RSS feed from the BBC World News website.

I need to find a way to set a rule that will highlight the country on page 1 if it is mentioned anywhere in page 2.

View 7 Replies View Related

Conditional Formatting For Multiple Worksheets?

Oct 23, 2013

I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?

View 2 Replies View Related

Multiple Conditional Formatting With Date?

Mar 31, 2014

Despite 'Google is my Friend' and lot of test, I got a bug in my formula. I'd like to colorize date between (Today + 30) and (Today + 60) The line with the xlsBetween operator, fail...

[Code] ......

Existing solution : I know how to bypass this issue, but it's really an awfull solution : fill all column with orange, and then the Conditional formating will overrun the orange color when nedeed. Works but I don't like it ...

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

Multiple Criteria In Conditional Formatting?

Jan 5, 2012

Start dateEnd Date01/01/201101/01/201102/01/201101/01/201110/01/2011

The above is the result that Im trying to get and I just cant work it out,

Basically,

If B is Blank Format Red
If B is populated and A minus B is less than 5 No format
If B is populated and A minus B is Greater than 5 format Red

-- Im sure its easy but I just cant get it

View 6 Replies View Related

Conditional Formatting Multiple Cells

Jan 28, 2014

I need to conditional format a cell based on it's current value and a value in another cell.

For example:

Each person would have a different target. So if Steve has a figure of over 20 his highlights green, Paul 12 etc.

However each person would not appear in the same row each day.

The Conditional Forumla of =(A1="Steve.Smith")+(C1>="20") works, but then applying to all cells messes it up a bit.

25

Paul.Smith

14

Ian.Smith

15

Jim.Smith

20

John.Smith

18

View 5 Replies View Related

Conditional Formatting With Multiple References

Jul 18, 2014

I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.

What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.

My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.

View 1 Replies View Related

Conditional Formatting In Multiple Cells

Oct 25, 2007

I am having a problem setting conditional formatting for multiple cells.
The first condition of the conditional formatting works for the cells but the second condition does not. There is probably an easy fix for this but I can't seem to find it.

View 9 Replies View Related

Multiple Conditional Formatting On Text

Aug 7, 2006

I need to employ conditional formatting to text on a spreadsheet - but using more than the 3 colours provided from the Format>Conditional Formatting menu.

I recently purchased the Excel Hacks (100 Industrial-Strength Tips & Tools) book by David & Raina Hawley, Hack #92 details how to get around Excels 3-criteria limit for conditional formatting using VBA, but this solution only describes multiple formatting on numbers, not text. For example, if each letter of the alphabet were entered into separate cells, I would like each to appear in a different colour automatically.

View 3 Replies View Related

Complicated Conditional Formatting With Multiple Columns?

Apr 22, 2014

I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.

So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.

If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)

The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"

Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)

apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.

View 5 Replies View Related

Conditional Formatting For Multiple ActiveX Controls

Aug 11, 2014

I have a workbook with multiple sheets. I want to add conditional formatting for a cell on 1 worksheet based on if multiple ActiveX controls are checked (checkboxes) on another sheet. I have 10 checkboxes (now LinkedCells named "G1" -> "G10") on worksheet "Data Entry". The formula should be if all 10 boxes are checked on the "Data Entry" worksheet, cell "A1" on the "Milestone" will be gray. Any clues on how to write the formula for this Conditional Format?

View 1 Replies View Related

Conditional Formatting Of Duplicates Across Multiple Cells

Feb 25, 2014

I use the conditional formatting of duplicates within a column quite often. Now I am trying to highlight duplicates based on multiple cells in the same row. I can't seem to get a formula to make that work. I have four cells in separate columns but the same row, for example H2:K2, H3:K3, H4:K4, etc. I would like to highlight the four cells in that row if there is a duplicate of all four cells in another row. So if one, two or three cells are duplicates in the same column I do not want to highlight any of the cells, it should only highlight the group of four cells if all four cells are identical in another row. Pretty much the same way the default highlight duplicates works, just the value is spread across four cells.

View 2 Replies View Related

Conditional Formatting Multiple Rows Independently

Apr 22, 2009

I have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know. Sample worksheet attached.

View 2 Replies View Related

Multiple Criteria Per Conditional Formatting Rule

Sep 9, 2009

Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10

Or if the cell contains a "Z" or a "C" ....

View 13 Replies View Related

Multiple Criteria Per Conditional Formatting Rule..

Sep 18, 2009

how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?

View 2 Replies View Related

Conditional Formatting Duplicate Value With Multiple Criteria?

Aug 21, 2013

formatiing a cell, e.g. change color to RED, in the event the value is a duplicate. To make it clear, I need to show that if the Serial Number of a product just entered is a duplicate and should change color when a combination of conditions is true. i.e. if a "part number" AND the currently entered "serial number" already exist, the entered serial numbers should change color.

View 3 Replies View Related

Conditional Formatting Duplicates Across Multiple Sheets

Aug 13, 2009

Conditional formatting duplicates across multiple sheets. I have been using the formula

View 5 Replies View Related

Applying Conditional Formatting To Multiple Cells?

Apr 25, 2012

If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?

View 1 Replies View Related

Conditional Formatting If Multiple Cells Contain Same Text

Dec 30, 2012

I want to conditional format based on all of the following cells contain the same text as AT19 or not.

A19 H19 O19 V19 AC19 AJ19

What is my formula?

View 1 Replies View Related

Conditional Formatting Based On Multiple Inputs?

Feb 19, 2013

I'm working on a fairly complex spreadsheet where I want to pull conditional formatting into an entire row. I have a column in tab1 where I have a bunch of beer styles listed. On tab2, I have a 2 columns: a complete list of styles with a 'yes' or 'no' in the next column based on certain criteria. What I'd like to do is have the column in tab1 highlight if that style in tab2 has a 'no' in the column next to it.

View 3 Replies View Related

Conditional Formatting Of Cells In Multiple Sheets

Sep 6, 2013

I have twelve sheets that each have a total score on cell G10 that I want to have copied to a thirteenth sheet in a specific column (F5-16). What would I have to do to make that happen? The G10 cell is an auto-summed cell, if that makes a difference.

Also, is there a specific way to create formulas?

View 3 Replies View Related

Conditional Formatting For Multiple Columns And Rows?

Mar 28, 2014

I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.

I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.

Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.

View 3 Replies View Related







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