Excel 2013 HELP! Macro that will return a value based off of Conditional Formatting Color

Originally Posted by dflak

I will offer the same advice I've seen whenever this question is asked: don't try to get the conditional formatting color. Use the criteria that sets the conditional formatting color to do whatever it is you want.

Hi, i've tried that but the formula doesn't seem to work. Below is the conditions i have set in the cells, any suggestions would be appreciated:

Green: AND(J2<-5000,LEFT(I2,3)<>"541",LEFT(I2,3)<>"540")
Red: AND(J2-L2=J2,J2>1000,LEFT(I2,3)<>"541",LEFT(I2,3)<>"540")
Yellow: AND(J2/L2>120%,LEFT(I2,3)<>"541",LEFT(I2,3)<>"540")

Originally Posted by dflak

I don't see how your conditional formatting is working at all. Columns I, J and L are blank.

Sorry i modified worksheet before uploading, in the uploaded sheet column J = Column B, Column I = Column A, Column L = Column C. The attached worksheet reflects these changes. Thanks

Delete____

Originally Posted by dflak

I lifted the real conditions from the spreadsheet itself

=AND(B2<-5000,LEFT(A2,3)<>"541",LEFT(A2,3)<>"540")
=AND(B2-C2=B2,B2>1000,LEFT(A2,3)<>"541",LEFT(A2,3)<>"540")
=AND(B2/C2>120%,LEFT(A2,3)<>"541",LEFT(A2,3)<>"540")

That would be correct.

Originally Posted by dflak

In the attached, I have some helper columns that can be deleted. I left them there because they show the intermediate steps to developing the ultimate formula.

These three columns are the criteria for the conditional formats. Note that Yellow has a couple #DIV/0! errors. Column H has the first step of the formula which is basically =IF (Green, "Credit Received", IF (Red, "Credit Not Received", "Something Else")) - you didn't say what to do about not green and not red.

Column I is the same formula except I substituted the actual formulas for the intermediate cells).

This is AWESOME. Thank You So Much.