Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting rows in a range where ID code does not exist in secondrange...
I need to highlight (e.g. by font or background colour) rows which
contain a value that is NOT present in a reference range. Column S of the reference range contains an account ID code. In the range being compared, I want to highlight new accounts which do not exist in the reference range. The new range has a different layout to the reference range because its a CSV file of payment data downloaded from an assortment of third party web sites, but lets just assume for the current example the ID code is in column C. I know there are a variety of methods I could use including highlighting all and then looping through the IDs in the reference range, unhighlighting all rows where the ID appears in the new range. But is that the fastest way? What is the most efficient way to code for "if this ID code doesn't appear in the reference list, highlight the row in bright blue"? Travis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting rows in a range where ID code does not exist in secon
I'd probably do this with a Conditional Format and put something like this in
the Formula for the VLOOKUP =ISNA(VLOOKUP(C1, ReferenceRange,1,False)) Change the VLOOKUP to suit. if you want to do this for all of column A for example, highlight column A and put the first cell that you've selected in the VLOOKUP. Make sure it looks something like $C1 with no $ before the row. If you need a VBA example, come back. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "travis" wrote: I need to highlight (e.g. by font or background colour) rows which contain a value that is NOT present in a reference range. Column S of the reference range contains an account ID code. In the range being compared, I want to highlight new accounts which do not exist in the reference range. The new range has a different layout to the reference range because its a CSV file of payment data downloaded from an assortment of third party web sites, but lets just assume for the current example the ID code is in column C. I know there are a variety of methods I could use including highlighting all and then looping through the IDs in the reference range, unhighlighting all rows where the ID appears in the new range. But is that the fastest way? What is the most efficient way to code for "if this ID code doesn't appear in the reference list, highlight the row in bright blue"? Travis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting rows in a range where ID code does not exist insecon
On Oct 1, 6:23*pm, Barb Reinhardt
wrote: I'd probably do this with a Conditional Format and put something like this in the Formula for the VLOOKUP =ISNA(VLOOKUP(C1, ReferenceRange,1,False)) * Change the VLOOKUP to suit. * if you want to do this for all of column A for example, highlight column A and put the first cell that you've selected in the VLOOKUP. *Make sure it looks something like $C1 with no $ before the row. If you need a VBA example, come back. Conditional format sounds like a good solution. How do I set it for a large number of different values though? Lets just say my reference range (range of customers already entered) has a column with the following values: ID101 ID102 ID106 ID108 And the range where I'm trying to find new values has the following values ID101 ID105 ID106 ID108 I'd want the entire row of the new range which has ID105 in it to be highlighted red. That's easy enough for setting one row. I've successfully done that by entering the formula =ISNA(vlookup({the address of the cell containing ID105},S:S,1,false)). That's one cell done. I can copy that formula for the remainder of the row and then the row gets its highlight. But how do I transpose it down? In the actual spreadsheet there are a thousand rows being checked so manually changing the formula for each of them is a pain. I can of course write a macro that would apply the format automatically. I set up a conditional format with the macro recorder running and got some code that looks pretty easy to turn into a loop that will run through the whole table setting up the conditional formatting. But maybe that isn't necessary. How do I copy the formula down so the following row is formatted with the formula =ISNA(vlookup({the address of the cell containing ID106},S:S,1,false))? Travis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting rows in a range where ID code does not exist in s
=ISNA(vlookup({the address of the cell containing ID105},S:S,1,false)).
Let's say you select all of the cells you want to check. For this example, I'm going to say it's C1:C1000 and you start on cell C1 In the conditional format, put this =ISNA(vlookup($C1,S:S,1,false)). Does that work? -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "travis" wrote: On Oct 1, 6:23 pm, Barb Reinhardt wrote: I'd probably do this with a Conditional Format and put something like this in the Formula for the VLOOKUP =ISNA(VLOOKUP(C1, ReferenceRange,1,False)) Change the VLOOKUP to suit. if you want to do this for all of column A for example, highlight column A and put the first cell that you've selected in the VLOOKUP. Make sure it looks something like $C1 with no $ before the row. If you need a VBA example, come back. Conditional format sounds like a good solution. How do I set it for a large number of different values though? Lets just say my reference range (range of customers already entered) has a column with the following values: ID101 ID102 ID106 ID108 And the range where I'm trying to find new values has the following values ID101 ID105 ID106 ID108 I'd want the entire row of the new range which has ID105 in it to be highlighted red. That's easy enough for setting one row. I've successfully done that by entering the formula =ISNA(vlookup({the address of the cell containing ID105},S:S,1,false)). That's one cell done. I can copy that formula for the remainder of the row and then the row gets its highlight. But how do I transpose it down? In the actual spreadsheet there are a thousand rows being checked so manually changing the formula for each of them is a pain. I can of course write a macro that would apply the format automatically. I set up a conditional format with the macro recorder running and got some code that looks pretty easy to turn into a loop that will run through the whole table setting up the conditional formatting. But maybe that isn't necessary. How do I copy the formula down so the following row is formatted with the formula =ISNA(vlookup({the address of the cell containing ID106},S:S,1,false))? Travis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting rows in a range where ID code does not exist in s
On Oct 1, 9:23*pm, Barb Reinhardt
wrote: =ISNA(vlookup({the address of the cell containing ID105},S:S,1,false)). Let's say you select all of the cells you want to check. *For this example, I'm going to say it's C1:C1000 and you start on cell C1 In the conditional format, put this =ISNA(vlookup($C1,S:S,1,false)). Does that work? Actually, yes, it did. Thanks. Travis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Option Button Code (Highlighting Rows) | Excel Programming | |||
range name does not exist | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
does code for this exist? help please | Excel Programming |