Using muitiple ranges within a loop to process if function..

**jordanleewillis** · 05-23-2016, 11:01 AM

Hi All,

My first post on here so hopefully will be useful.

I am writing a validation program in VBA and I can't figure out how to use muitiple ranges and if statements within'. Here's my requirements:

- If a cell in column1 = 1 and on the same row column2 > 5 then colour that cell in column2 red

I have used "for each cell in range ..." in a linear way to apply speicific column validation but I can't work out how to cross column check.

Any help would be hugely appreciated.

Many thanks,
Jordan

**walruseggman** · 05-23-2016, 11:26 AM

No need to loop through the cells:

Please Login or Register  to view this content.

**jordanleewillis** · 05-23-2016, 11:39 AM

Hi Walruseggman

Thanks for your promp reply. Could you break that down for me.. I am using named ranges .. I understand the code is filtering on the requirements but not sure how to pop in my named column ranges?

Cheers,
Jordan

**walruseggman** · 05-23-2016, 12:03 PM

Assuming from your original post:

column1 (the =1 column) is named "name1", and
column2 (the >5 column) is named "name2", then:

Please Login or Register  to view this content.

If this isn't what you want, please post an example workbook with sample data and expected output.

**jordanleewillis** · 05-24-2016, 04:33 AM

Ran your script with my ranges in and getting Error "Medthod 'range' of object'_global' failed"

**walruseggman** · 05-24-2016, 09:19 AM

please post an example workbook with sample data and expected output.

*******************

**jordanleewillis** · 05-24-2016, 09:32 AM

Hi Walruseggman,

Sorry; Here's a link to the workbook:

http://www.excelforum.com/attachment...1&d=1464096566

As you can see "name1 = BLDGCLASS"
"name2 = NUMSTORIES

In this work book the rules will be applied to columns J and M.

Many thanks,
Jordan

**walruseggman** · 05-24-2016, 09:51 AM

Link is broken. Can you use the picture icon (with the tree) to try to upload, please?

**jordanleewillis** · 05-24-2016, 10:01 AM

sample data 0516.xlsx

That better?

**walruseggman** · 05-24-2016, 11:41 AM

So i realize it was an innocent mistake, but be careful with the term "named ranges". Named ranges are an actual thing in Excel, where you can assign a group of cells a name and then refer to that name instead of the group of cells (Ctrl + F3 gets you there). And that's how the code i provided works.

So two questions:

With the sheet you uploaded, it looks like you just meant you have column headers that have the names you mentioned. Just to be sure, can you clarify which approach you meant? Named ranges, or column headers?
Your original post says you want =1, >5 highlighted, but you have a 2, 37 highlighted and nothing else. I would have expected Columns M cells in rows 2, 3, and 7 highlighted. Whats going on here?

**jordanleewillis** · 05-24-2016, 11:44 AM

Hi Walruseggman

1 - Yes I meant column headers; in SAS code which I am more fmailiar with we use named variables/ranges so this is my mistake
2- Slight change of logic to =2 for 1st criteria

Thanks for your help again

**jordanleewillis** · 05-24-2016, 11:44 AM

Hi Walruseggman

1 - Yes I meant column headers; in SAS code which I am more fmailiar with we use named variables/ranges so this is my mistake
2- Slight change of logic to =2 for 1st criteria

Thanks for your help again

**walruseggman** · 05-24-2016, 12:10 PM

Please Login or Register  to view this content.

**jordanleewillis** · 05-24-2016, 12:25 PM

That has done it; though I added the following snippet after the auto filter clear so that the first row was not highlighted:

Please Login or Register  to view this content.

Many thanks for your help with this

**walruseggman** · 05-24-2016, 12:47 PM

You probably don't want to do it that way, as Active Cell could be anything, and you don't need specialcells here anyway. I would do:

Please Login or Register  to view this content.

**jordanleewillis** · 05-25-2016, 05:41 AM

Perfect thank you

Using muitiple ranges within a loop to process if function..

LinkBack

Thread Tools

Rate This Thread

Display

Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Re: Using muitiple ranges within a loop to process if function..

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] sumproduct muitiple criteria with greater than function

Repeat or Loop Process for each value in a list for saving new filesin VBA

[SOLVED] how can I take the value of several selected cells and process them in a loop ?

Spawn a child process? to run the loop, then move on

[SOLVED] LookUP formula matching muitiple conditions

VBA to create specific named ranges and repeating process (Loop?) across columns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions