How can I write a macro to lookup a value in left column in an Excel sheet and name the range that value ?? Thanks for your help! K x

Workbook.Sheets("Sheet1").Cells(1,1).Value is how you get a value for a cell. More information on what you are trying to do would help. (i am not certain what "name the range that value" means) Hope This helps. Chi Happens "They mostly come at night...mostly"

Hi Chi On a monthly basis downloading info which will change in size every month. I am using formulas to look in a named range for specific criteria. Now how would I name these ranges automatically every month. Left column will contain the data to name the range eg rows 1 to 12 will contain 100, 13 to 18 = 200, 19 to 26 300 so on. Next month might be rows 1 to 18 = 100, 19 to 22 = 200. Hope you understand what I want to achieve. Thx again K

KaseyLeigh, Ok I am not certain I completely understand but I am using your info to create an example sheet. I will post it here: http://www.evolutionsoftwarellc.com/downloads/for_kasey_leigh/test.xls when I finish it. Download it and let me know if this is what you mean. If not, then email me an example of what you mean. It is currently 9:32 AM (est) I will try to have the test uploaded by 10:00AM est. Chi "They mostly come at night...mostly"

KL, Okay, it is online. I tested it by selecting OPEN when I clicked on the link and I get an error in the range selector. But when I chose SAVE and then ran it from my computer (instead of within the browser) it works right. This is what it does: When you run the macro "RangeSelect" you get a little dialog box asking you to name the range (such as 100 or 200...) then when you click "Get Em" it clears the ranges and then select the range of the records matching the name and colors them green. The macro only selects A-G but you can easily change that. In addition, it does nothing with the range, just makes it purty. All the code (with the exception of the macro definition) is contained within the UserForm1 file. Let me know if this is what you meant or if it is completely off base. Chi "They mostly come at night...mostly"

Chi you're a genius! You're on the right track but .... instead of highlighting range with colour name the range "100", "200" so on. Also could it look down the whole selection eg ctrl + * look at left column for 100, 200 etc and permanently name the ranges. Hope you know what I mean! Thanks KL

KL, I am totally lost with the "NAME THE RANGE" thing. I have to admit I am not an Excel guru, just a VB one lol, so I don't know what that means. Please explain what "NAME THE RANGE "100"" means. It can look down the whole list so that the numbers do not need to be adjacent...let me make a modification and repost the code. while i do that, educate me on the "permanently name the ranges" portion. Thanks, Chi "They mostly come at night...mostly"