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.

The Problem: There is data in a step chart from left to right. On a separate step chart, Conditional formatting is set to lookup values from the first step chart and highlight non empty cells in the row. The formatting should only format the first non empty cell from left to right in the row. After formatting the first non zero value in the time line the formatting should cease. Note: the conditional formatting value and the value of the cell being formatted are not the same in either value or time but are related. The formatting is to illustrate the relation

Current Formatting formula:

=VLOOKUP($B395,$B$5:$DB$211,K$242,0)

Syntax for Vlookup(Lookup_Value,Table_array,Column_index_num,Range_Lookup)

Data for once source Column A shipping container numbers (alpha & numerical) Column B the container size code Column C relevant date of arrival

Data from another source Column D shipping container numbers (alpha & numerical) Column E the container size code Column F relevant date of shipment

What I want is: If Column A containers match with Column B containers Then in Column G the containers that matched and Column H the container size code Column I the date of arrival Column J the date of shipment

Vlookup seem to be an answer, but can’t get it to work. I am not good in VB, so may be formulas would be better Named range and IF & Match FORMULA may be?!! The list will be approximately 20,000 numbers long. Have attached the sample file.

I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.

For example, the data source contains different pets, their names, ages and their owners.

The other sheets are on a one-per-owner basis.

What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:

John Smith (in cell A1)

Pet - Name - Age ------------------- Dog - Rover - 3 Goldfish - Tom - 1 Gerbil - Chewit - 4

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?

I am trying to modify this formula to do one more thing, but I can't quite figure out how to do it. I have part 1 and 2, but part 3 is giving me a very hard time. The formula is not working correctly. It is supposed to do several things.

Do an HLookup for a row and return the last value in the row.If there is no value (blank), the value should be "Incomplete."If HLookup finds a value of "Fail," it should look to see if there is a value of "Pass" BEFORE (to the left of) "Fail." If this exists, then it should return a value of "OK."

Is there a way to specify conditional formatting using an Hlookup function? I want to format a cell with a specified color based on a horizontal lookup (see attached).

Basically, would like to have the following conditional formatting:

There is a fruit listed and the are person 1 and person 2. If the fruit under Person 1 or Person 2 matches the fruit under Fruit, the cell under either person 1 or person 2 highlights green; if it does not match, it highlights red. If the fruit has not been listed under fruit, such as in cell A3, cells B3 and C3 are not highlighted.

I would like to have excel check if eight of ten of cells in a row are above a certain criteria, then if they are format the tenth cell to be red...so excel checks the ten cells above and if eight of them are above a certain number then that cell shades red.

I think I need some version of a lookup for match function. Here is essentially what I need: I have a column of data points. I need to output every position at which the data point changes to a different value (going from top to bottom). See below for an example.

Here is some sample data (with column headers)

Position Data Pt

1 -7.5

2 -8.0

[Code] .......

Here is the result I need (I want to put a consistent formula into the second column below that returns the results shown in the second column below):

split 1 position 2

split 2 position 4

[Code] ........

Is what I am describing possible? Because I am inserting this into a template, I ideally need to do this outside of any sort of VBA work.

I'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.

I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).

What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17

Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.

I am having many difficulties getting conditional formatting using the match function to work correctly in my excel document.

What I would like to accomplish is as follows in the 3Q09 tab. If a subdivision name is found in C-62 through C-70 and a match is found for the subdivision name in AB-24 to AB-66 I would like it so the AB-24 to AB-66 Subdivision name is bolded for every match. I removed the function formulas from the 3q09 tab I used to have since they didnt work and I do not want to confuse anyone.

This is correctly done on the "Working Correctly" Tab included in the same file, so please view this for clarification if you need it. Why it works on one tab and not the other simply baffles me.

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))

In a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?

So in this example the values with an * would be highlighted.

I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

Worksheet (functions!)

This one has a list of numbers formatted as general. (Column G)

Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:

1) Blank 2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet) 3) A string that exactly matches an item in the lookup table 4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)

I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.

I have solved everything except for No. 4).

2) is taken care of by using "Format only cells that contain" with "No Blanks"

My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses =VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.

But 4) is tricky, because VLOOKUP can only return the result for a specific cell value. If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").

You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.

So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.

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).

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

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

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?