formula using text color

[jrtaylor]

I've attached a spreadsheet to illustrate my question.

I routinely copy/paste info to my worksheets. Sometimes the info is formatted so that negative numbers are expressed by using a red text color rather than a - sign or a ()

Is there a way to create a new, custom formula I can add to my formula bar so that I can include text colors in functions?

Obviously in this formula I'd use something like =if( A2 is red, A2*-1,A2)

Any advice? I prefer not to have to write macros. I've never been able to figure out where to "keep them" for future use.

Thanks!

[davegugg]

Unfortunately, using VBA is your only option as Excel has no built-in worksheet functions that do anything with cell colors.
However, creating a UDF is a piece of cake:

Please Login or Register  to view this content.

Put the preceding code in a standard module in your workbook to use it. Then just type GetCellValue() like you would any other worksheet function.

[barryleajo]

jr
using conditional formatting may also be of interest

[jrtaylor]

Davegugg, thanks! I can save the "module" language in a .txt file for future use.

I appreciate very much your help1

(Barryleajo, thanks but conditional formatting doesn't work for this issue. We need to obtain a cell's text color and then write a formula depending on the color.)