• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

compare two arrays a1:a7 compare to b1:b7, count how many are the same?

Hi, henksss!


I use DropBox and it's the same service to which you uploaded your original file.

I deleted the file from my local shared folder, opened it from my personal Excel folder, saved it, copied it to my local shared folder, waited until available at DropBox and could opened it.

Try again from same previous link. Otherwise you should wait and retry later, I guess.


Regards!
 
Hi, henksss!


I checked the web for if there were any issues with DropBox service and nothing reported at Google. Look at my screen: at left, my uploaded file in the cloud yet before 01:05 GMT-3, at right, no problem with DropBox.


Link:

https://dl.dropbox.com/u/60558749/compare%20two%20arrays%20a1_a7%20compare%20to%20b1_b7%2C%20count%20how%20many%20are%20the%20same_%20-%20conditional%20format%20vb%20%28for%20henksss%20at%20chandoo.org%29.png


Hope it helps.


Regards!
 
Aaaah I found it as well as yours is the right one Thanks so much.

here is the link to get my file. Hope some one can help solve this one


https://dl.dropbox.com/u/101518911/conditional%20format%20vb.xls


Regards Henk
 
Hi Henk ,


Can you see if this is what you were looking for ?


http://speedy.sh/kjRAD/Henk-conditional-format-vb-1.xls


Narayan
 
in coloum c insert formula a = b ie on c2 formula =a2=b2 you will get ans true or false just insert a formula of countif with range as the coloum with true or false ie C and criteria as "True" you will get the answer 3 Cheers :)
 
Thanks Narayank991 that is looking quite good and almost done but if I enter numbers in the future in cells d364,e364...k364 and onwards, it will give me the results in L,M364 but no colour changes.


Also in all the ones where there is colour now, I can remove the colours by clearing them without removing the results.


I would like it so if the results in L,M are there the colour gos with it fixed and the only way to remove the colour is to remove the results.


Thanks so much for your help it is almost there.


Regards Henk
 
Hi Henk ,


Since the method is using VBA , and not formulae , you will have to execute the VBA procedure each time you add data.


At present there is only one procedure for coloring the cells in column L and M ; if you want one more procedure for clearing all the cells of their colour , please let me know. I'll write it and upload the file.


Since the method is using VBA , and the procedure is not event driven , it is not really connected with the cells as and when they get fresh data. If you want that each time you add data to a row , the relevant cells should be coloured , let me know.


Narayan
 
Hi Narayank991 Thanks so much for your help. Every time I add data to a row I would like the colours to affect the cells as well, if that is possible?
 
Hi Henk ,


Today being Sunday , I hope you can wait till tomorrow. I'll upload the revised workbook tomorrow morning , around 4 AM GMT.


Narayan
 
Hi Henk ,


Can you see if this is OK ? Experiment by adding new data , as much as possible , before you can say everything is OK.


http://speedy.sh/VcnB4/Henk-conditional-format-vb-2.xls


I have defined a named range DataRange , so that as you add fresh data , and formulae in columns L and M , nothing needs to be changed within the macros.


Narayan
 
It seems to be working fine. I was wondering if it can be set up without the 2 buttons to colour and clear. It should auto clear when there is no data in C:I. and autofill as soon there is data and colour conditions are met.


I also need to copy this VB to another original spreadsheet and might need to change it to more rows like from row 3:1000 to row 3:5000. The selection of colours are good and are working correctly too.


Thanks very much for your help too.


Regards Henk
 
Hi Henk ,


The 2 buttons were given only because your file already has data in it , and you now wish to colour the cells in columns L and M.


If you are starting with a workbook which does not have any data , and you wish the cells to be coloured as you add fresh data , then just delete the two buttons. The Worksheet_Change event macro will colour each new row in columns L and M , as you add fresh data in columns A through K.


Auto clear is not really required since without any data , the cells will never be coloured. If you are going to change your already entered data and you wish to colour the cells based on the revised data , I think the event macro should still work correctly. I'll check this out.


If your data range extends to row 3000 or row 5000 , there is no problem ; the named range should take care of this. However , if you change the starting row from 3 to any other number , then you should change the formula , for the named range , in the Refers To box ; at present it is :


=Sheet1!$L$3:INDEX(Sheet1!$M:$M,COUNTA(Sheet1!$L:$L))


If the starting row is anything other than 3 , then the reference Sheet1!$L$3 should be changed to whatever is the new starting row.


Narayan
 
You must log in or register to reply here.
Back
Top