• 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.

How to find out value, if value is in color only not in text or numeric.

Any chance a redesign is possible? It's much better practice to mark cells with some sort of text to indicate values, and then use conditional formats to apply colors, rather than have user color cells and try to use that as information. The other headache is the use of merged cells. Merged cells are a MAJOR pain to deal with when writing formulas/macros. I would suggest using the "Center across selection" alignment rather than merging cells in the future.

With current setup, the only solution I see is either a macro or some kind of UDF.
 
Hi Ramesh, Hit Alt+F11 to access the Visual Basic Editor (VBE). Navigate to the module, and you'll see that Deepak has written as User DEfined Function (UDF) into the code. Code looks through the range, checking the color of the cells. Depending on what color is found, returns a value. Note that everything is hardcoded.
 
And how can we get value if value available in text or numeric.
Somendra Misra said:
Hi Ramesh,

Please see the attached file, with a probable solution with CF and formulas.

Regards,
Click to expand...


Hi ! SM Sir,

literally, you made it possible through formula, Hats off you!!

but i dint get it how you link with CF, will you please let me know, how its possible!
 
Luke M said:
Hi Ramesh, Hit Alt+F11 to access the Visual Basic Editor (VBE). Navigate to the module, and you'll see that Deepak has written as User DEfined Function (UDF) into the code. Code looks through the range, checking the color of the cells. Depending on what color is found, returns a value. Note that everything is hardcoded.
Click to expand...


Thanks Luke M Sir for making me understand!

actually that file is on office desktop, will see on monday.
 
Somendra Misra said:
@Ramesh Deo

In data table if you see I had entered Text values :) and Conditional format them to same color as fill color, so actually you can't see text and any time you change text automatically CF will change the format and formula will update itself to extract data.

Regards,
Click to expand...

Thank SM Sir,:DD
now i can see this, its a little bit mistake to analyse by me, anyway one thing tell me plz u made column header unmerged, is it possible, formulas will work with merged column header, suppose we have text value in place of color and column header merged in the same workbook.
 
Merged cells are always a pain, try to avoid them. The formula will not work with merged cells. Extraction formula can extract data but not with merged column header.

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