Cell colors not getting recognized by interior.colorindex

[Surya51]

Hi Friends:

This has proved to be a very tough task for me and beyond my reach. I was trying to extract the latest (topmost) colored cell values from certain columns in the workbook and a forum member has come up with the following code for this purpose. This code works fine for normal formating. However, my cell colors are set through conditional formating and therefore these cell colors are not recognized by interior colorindex or color functions. I would therefore appreciate if our forum members can help me out in modifying this code (or alternatively provide another code) to be able to recognize the cell colors and return the relavant values. I am describing my problem and my requirements in details.

I have uploaded a sample workbook here. In this workbook, the cells in column G (colorindex 35) and J (colorindex 40) are colored through the following formulas:

column G: formula =Large(G3:G30,1)<G2 interior color index 35
column J: formula =Small(J3:J30,1)>J2 interior color index 40

There are 450+ such files in a folder and I wish to extract the latest colored cell values (in a new workbook called HiLoList.xls created thr this code), alongwith relavant column A values from these files. Files are in Excel 2003, have one sheet each and are properly sorted. I don't know much of VBA and not in position to tweak the code suitably. I think there could be two approaches. One, to modify this code to recognize the cell colors set thr the above conditions and then extract the required cell values. Second, convert the conditionally formated cell colors to normal highlighted cell colors, so that this code itself can do the job. (Maybe, this could also be done by running a suitable code to convert the conditionally formated workbook to standard formated workbook). I would appreciate any help in this matter.

Please Login or Register  to view this content.

[protonLeah]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

For the future, please wrap your VB code in [CODE] tags not [QUOTE] tags.

[Surya51]

Hi protonLeah:

Thanks for your quick response and the code. I am sorry, I have inadvertantly wrapped the code in quote tags which now I have corrected to code tags.

I would like to once again explain my exact problem, requirement and expectation in detail.

I have a folder named "D:\FOScrip" which contain roughly 450 odd files. I have used conditional formating to color cells in column G with light green color (Index 35) and cells in column J with tan color (Index 40) for all the files in the folder. I wish to extract the cell values of the uppermost (latest) colored cells (with relavant column A date values and file names) and generate a sort of report in a new workbook (to be subsequently saved as HiLoList.xls) in the following format.


1-----A-------------B--------C-----------D--------E
2-----File Name-----Hi-------Date--------Lo------Date--------<----Header Row
3-----DCB.xls------48.45---16-Jun-12--38.20---27-Jun-12-----<----First file values


The code that I have posted here does go thr all the files in the folder and return the required values IF the colors are set thr normal formating but since the colors in my files are set thr CF, it is not able to detect and return such cells values. I therefore need a code that is able to detect colored cells set thr CF and generate the report as described above. Unfortunately, though your code does detect CF colors, it does not generate the required report. As you can see, I am interested only in the uppermost colored cells (index number and CF formula is immaterial because every column has only one color index, 35 for G and 40 for J) in columns G and J (and not all the colored cells in these columns). I would therefore highly appreciate if you could tweak my code appropriately (using the methodology that you have used in your code to detect CF colored cells) to generate the above report or devise an altogether new code to achieve the above result. The ultimate objective is to generate the HiLo report in the prescribed format.

Thank you once again for your kind assistance and efforts.

[Surya51]

Hi protonLeah:

I hope you have clearly understood my exact requirements with the above explanation. Should you need any further clarification, please do ask me. I am eagerly awaiting your further response to my thread based on the above inputs.

Thanks.

[Surya51]

Hi protonLeah:

I have not received any sort of feedback from you either to my last two posts or to the PM that I had sent a few days back. I would particularly like to know if there can be a solution at all to the problem that I had described in detail (So that I can think of approaching other members or other forums if for any reasons you are not in a position to provide a solution). I hope you are getting email alerts of my posts to this thread. Thanks.