how to have excel look at 4 columns and return the value of the colored cell

[Rique]

Hi,

Please refer to attachement.

I am looking for a way to look at Price 1, Price 2, Price 3 and Price 4 and return the value that is colored in Green to column named Price.

Your suggestions are greatly apprecicated.

Thanks
Frederique

[Moo the Dog]

What is the logic behind the green cells? If there is logic, you can do it with a formula. Otherwise, if there is no standard logic behind which price is colored green, then you will have to use VBA to find which cell in each range has the colored fill and use that price.

- Moo

[reachharry]

Hi,

Please find attached the file with the solution. I have used macro and designed a custom function called "coloredprice" which will look into four cells and whichever is green shaded it will be returned as the output.

Hope this helps..

Regards,
Hariharan

[Rique]

Hi Moo,

There is no logic behind which price is colored green.

Hi Hariharan,

Thank you for the attached file.

Are you able to explain the steps you took to create this function / Macro?
When open the spreadsheet the answers are there however if I click in the cell it changes to #NAme?.
I would like to have a look at the function / Macro and see if I can recreated it for the workbook that i'm working on.
The attached Query2 was put together for examples purposes.

Your help is greatly appreciated.

Thanks
Rique

[vlady]

hit alt+f11

[reachharry]

Hi,

Sorry for the delayed response, I was in excel training session for one of my corporate client.

Here it is,

1. When you open the excel file which i have attached in my earlier response, you have to enable the macros else the function would not work.
2. How to enable the macros, instruction is available in the attached word document.
3. If you want to have a look at the function which I have developed, you need to press ALT key and F11 key together, this would take you to Visual Basic Editor screen. This is the screen which we use for writing our custom functions. In this you need to find "Module" in the project explorer, and when you double click it, it would display the coding. Process is available in the same word document.
4. Sometimes it is possible you would directly see the code itself the moment you press ALT key and F11 key, in that case you don't have to do anything extra.

Hope these instructions are clear for you...

Wish you a happy weekend

Hariharan