Writing a macro that compares values from two or more columns

Posted by Amit on January 25, 2002 9:41 AM

Hi,
I have a huge excel file. I want to write a macro in VBA which compares the values of two columns row by row. For eg. There are two columns K and M. Either of the column can have any value. Now i want to compare the value of K and M with another column Z. To explain this point more ... lets say K for row 10 has a value "Closed" and M for that row has "Open" Now for this row i want to see if Z has either of the values (Closed or Open). If Z for row 10 has either of the values then the macro should do nothing otherwise the macro should highlight Z with a background color.
This has to be done for the entire excel file.

Could anyone help me with this.

thanks
amit

Posted by Barrie Davidson on January 25, 2002 11:10 AM

Do you need a VBA solution

Amit, why not conditionally format the cells in column Z. Conditionally format Z1 using this formula =OR(Z1=K1,Z1=M1). Then define the format (background colour) you want should the condition be true.

Does this help?
BarrieBarrie Davidson

Posted by Amit on January 25, 2002 11:56 AM

VBA solution would be better!

Hey Barrie,
Thanks for the above solution but would u know how to do this using VBA. I am looking for a macro written in VBA that would do the needful.

Thanks

amit

Posted by Amit on January 25, 2002 12:04 PM

The problem with conditional formatting

Barrie,
The thing is that i would want to loop through all the cells of those three columns. There are like 6000 rows in this file i have to work on. So i guess I would want a macro that could be a solution for all these rows.

amit

Posted by Barrie Davidson on January 25, 2002 12:18 PM

Re: The problem with conditional formatting

Sorry Amit, I don't think I was clear enough on my response. Once you format the first cell, you can copy that format all the way down column Z.

Does this work?
BarrieBarrie Davidson

Posted by Amit on January 25, 2002 12:36 PM

Re: The problem with conditional formatting

Barrie,
Yeah that is a possibility. I'll work this out and see. Maybe then i could record the whole thing into a macro. Think that should work.

Thanks
amit

Posted by Amit on January 25, 2002 1:06 PM

Cant copy conditional format

Barrie,
I have defined the conditional format for a particular cell but i cant seem to copy the format to other cells. Also the reference in this case should be relative right! like M1 or Z1 , M2 for Z2...

If i try copying it as i do for copying a formula it just copies the value.

AMit

Posted by Barrie Davidson on January 25, 2002 1:26 PM

Re: Cant copy conditional format

Amit, copy Z1, select the range you want to paste the formatting to, and then select Edit|Copy|PasteSpecial. From the Paste Special box, click on the Formats radio button. This should do it for you.

Regards,
BarrieBarrie Davidson