# Conditional Formatting IF Function To Identify Past Due Items In Red And Items Completed

Aug 13, 2014

I have a spreadsheet that lists a set of actions and the days they need to be completed.

The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.

I have a dashboard on a separate worksheet, showing if the item is complete and the due date.

Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.

I have been going through my books and trying to solve this, but I run into a different issues.

Even if for now If I can get the main worksheet to display the color conditional formatting that would work

## Conditional Formatting: Count The Number Of Items On The Right And Hightlight Them With A Color

Dec 22, 2009

I have attached a copy of my "budget". What i need is whenever you choose a option in A9 on PayCheck - DEC - 09 - B it will count the number of items on the right and hightlight them with a color. I use =COUNTIF('PayCheck - DEC-09-B'!E\$2:E\$1000,A9) in A11 to tell me the number of occurences but I would also like a visual effect with colors.

## Identify Items In List Existing In Another

Nov 2, 2006

In an excel file I have 2 lists of files. List #1 has about 6,000 filenames and List #2 is a subset of List #1.

I am looking for a solution which will help me highlight/identify those files which are also in list#2.

Ofcourse one way is to sort both lists and then place them in consecutive columns (say column A and column B) and then in another column (column C) use a IF function to identify if A1=B1 (or A1 <> B1) and then take it from there. However this approach helps a litle becuase there may be a gap in List#2 and thus for a great # of cells in column C i will end up getting false answer. Hope you're following me??

The underlying requirement for this is rather lenghty so I'm skipping those details. However I would like to share that so far and whenever I come across doing this exercise (about 3-4 times a month), I ended up achieving the results but it takes me hours.

## IF Formula - Identify New Stock Items In A Spreadsheet?

Mar 27, 2009

I have two spread sheets from one of our suppliers, one was issued in 2008 and the other 2009. The newly issued sheet has about 400 extra items. How can I identify these new items of stock easily. I have attached both sheets for you to look at.

## Identify Duplicate Lines Items And Generate Report.

Mar 11, 2009

I have a report with ~44,000 line items. From this report, I need assistance generating a report that lists all unique line items. I am having problems trying to identify which line items are duplicates as I need to compare 4 different cells in each line to other lines to determine if it is a duplicate item (compare data from columns A, B, E, F)

For example, raw data may be as follows:
Column A………Column B…….Column E…………….....Column F
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….22222222…….239103302000………..222.22
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….33333333…….353496508000………..333.33
10/13/2008…….44444444…….239103302000………..444.44
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008 ……11111111…….353496508000………..888.88............................

Note from above, there are two occurrences of a duplicate item. As a result, the output table would only list this item once.

I have attached a sample of the spreadsheet with dummy data on one tab and the desired output on another tab. Note that all the data is broken up into blocks of data that is separated by data that is only in the first column of the row.

## Getting Conditional Formatted Dropdown List Items To Keep Their Formatting In List

Feb 12, 2014

I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.

## How To Generate Dropdown List With Data Which Uses Column To Identify Items

Apr 18, 2014

I have in column A and B. A includes two (or may be more) kind of items, i.e. drink and snack. Column B has for example water next to drink and chips next to snack. The list is huge.

How can I generate a drop down list with data which uses the A column to identify the items which it should include. I want to make two separate drop down lists for drink and snack and each should include only those options which belong to those categories. I.e. drink list should be like water, cola, pepsi, etc. The problem is that the list is huge and it's not possible to sort it. Some kind of if statement structure with named range? The drop down should be dynamic in a sense that if I add line to data, it should be picked to the right drop down menu.

## Count Items In Column That Match Multiple Data Items?

Mar 27, 2014

I need to count the total number of times 4 different values appear in a column. This formula works for one value:

=COUNTIFS(Source!\$C:\$C,\$B5,Source!\$J:\$J,\$L\$3,Source!\$L:\$L,\$H\$1)

Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.

## Select Multiple Items In List And Then Print Those Items?

Dec 26, 2013

I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.

In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.

## Excluding Selections: Added To The 20 Items And Not To The 3 Items

May 23, 2006

If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..

## Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX(\$G\$1:\$G\$760,SMALL(IF(ROW(\$G\$1:\$G\$760)=MATCH(\$G\$1:\$G\$760,\$G\$1:\$G\$760,0),ROW(\$G\$1:\$G\$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

## Function To Calculate Frequency Of Items

Feb 27, 2007

I have a matrix w/ alpha characters in a 10x10 grid. I want to see how many times each letter shows up and then rank them asceding.

What is the function to do this? I looked at Frequency and Count, but do not think either will do the required work.

## Using Conditional Formatting To Identify Symbols

Dec 9, 2011

On my spreadsheet, I have used an IF statement formula to get rid of any < values that I have in my tables e.g.

## Look Up Function Is Coming Up With The Wrong Results On Several Items

Oct 16, 2009

The attached workbook contains a cell with validation list selection and a look up function. The validation list selection works fine, but the look up function is coming up with the wrong results on several items. Is this a bug or am I doing something wrong?

## Create Function To Balance Items That Are Subordinate

Jan 29, 2007

I am trying to enhance our estimating spreadsheet. If you look at the example attached it is a snippet of the spreadsheet. What I am trying to do is make column AJ (balanced bid unit) include subordinate items. The way a sub item will be defined is column B (bid item) if column B = S, then its balanced bid unit need to be reflected in the column AJ. I would also any item titled as Y in column B (bid item) to include all listings as S (until next item as Y) in the column AJ totals.

## Conditional Formatting To Identify Change In Signs?

Jun 26, 2014

So I am working with a model that calculates IRR, NPV, etc. I need to create a formula for conditional formatting that calls out if the IRR calculation is invalid due to non-normal cash flows. These are cash flows where signs change more than once (eg -100,200,-300).

## Populating Multiple Items With Validate Function In Separate Columns

Mar 28, 2014

UREC Bar Event Sheet.xlsx

I am trying to correspond prices with products from the validate function in multiple columns. I want to select the product and have the price for the product populate in the 'cost' column. I have the list of products with corresponding prices on sheet 2 named 'products'. I have attempted to write a formula in cell G11 on the Event Sheet, but was unsuccessful. Please direct my path.

## Use The COUNTIF Function Formula To Count The Number Of Items In Column A?

Mar 7, 2014

We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.

Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.

In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.

TYPE
MTD Closing Date
Status[code]......

## Conditional Formatting: Box That If The Cell Populates "Completed" Or "X Required", Then Highlight As Green

May 12, 2009

I have the folowing criteria :

1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green

I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.

## Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

## Nesting Ceiling Function In IF Function - Conditional Formatting?

May 13, 2013

I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)

right now it looks like:

If (C10

## VBA Function Composite Score Needs To Be Completed?

Aug 11, 2012

I am trying to come up with a composite score that if a child takes a particular course and has a grade then make a composite score. Here is an example below.

MathScienceReadingWritingÂ Composite Score Looking for2APGEAPAPÂ A+BC-B-Â 11003

In the example below I am looking for a composite score of 1100. Here is how I derived at it!
=4*100 + 3.2 * 50 + 2.4 * 100 + 3.0 * 100 = 1100

Code:
Function composite(r As Range, course As Range) As String
Dim d As Double
Dim score As Double

[Code]....

## Conditional Formatting (max If Function)

Feb 8, 2014

I would like to use conditional formatting to highlight the max value if the constraints are not violated.

For example,
Row 91 - Profit
Row 94 - Constraint A (750<=A<=855)
Row 95 - Constraint B (12<=B<=17)
So, I tried to type this in conditional formatting but it didn't work ><
=MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),\$B\$91:\$Q\$91,0))

## Can We Do OR Function In Conditional Formatting?

Dec 14, 2009

Is it possible to use OR function in Conditional Formatting? I want to do the following.

If A1 = 1, A2 cell is filled in Red
If A1 = 2, A2 cell is filled in Red
If A1 = 3, A2 cell is filled in Green,
If A1 = 4, A2 cell is filled in Green,

I only managed to do the above for 3 conditions as Conditional Formatting only allows me to no more than 3.

## How To Use Between Operator In AND Function While Doing Conditional Formatting

Oct 5, 2013

I have three columns named "Type" , "start_date" and "end_date".

Type column can hold only "A","B" and "C".
start_date and end_date columns are time stamps. ex: 9/14/13 9:35 AM

I want to do conditional formatting like,

If type = "A" and (end_date - start_date) value between 30 and 40 (in hrs) then it should be amber.

I did for greater than and lesser than values, but I am facing problem while doing the above one.

## Date Function (conditional Formatting)

Sep 24, 2008

I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).

## More Than 3 Conditions For The Conditional Formatting Function?

Apr 24, 2002

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

## Conditional Formatting On MATCH / LOOKUP Function?

Mar 6, 2012

I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.

So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.

The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..

Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.

Length cell formula:

=OFFSET('Reference- Materials'!\$N\$5, MATCH(\$BO32, Materials_Item_Number, 0), 0) * \$AH32

## Excel 2010 :: Replicate Conditional Formatting Using A User VBA Function?

May 6, 2014

Using Excel 2010.

The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)

In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.

The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8

I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".

VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8

On Error Goto handler [code]....

There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across

Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the

same errorExecuting the subroutine on its own (with the absolute range) works OK

So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function

## Macro Fill Down Function Is Changing Format In Conditional Formatting

Dec 18, 2013

When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)

test2.xlsm

Is there a way for the Macro code to bypass this issue?