Need help with a formula that I need to ignore any colored cells

[Phantasm]

I have a formula to prioritize jobs on a worksheets that takes into account the days remaining until the jobs due date & also the operations of the job. I quantify each operation to a number that equals the days that I think the operation will take. Here is the formula:

=SUM(INT(E2-TODAY()))-(SUM(COUNTIF(G2:N2,{"SAW","3-AXIS","BP","MILL","BLANCHARD","RAL *","HARDEN","ASSEMBLY","BLACK ZINC","BRIGHT ZINC","BEND","BLACK ANODIZE","KEYSLOT","SPLINE","SAND","HELICOIL","CLEAR ANODIZE","WILLIAMS","WELD","LATHE","EPI"})*{0.5,1,1,1,5,5,2,0.5,1,1,3,5,5,5,0.5,0.2,1,5,2,1,5}))

E2 = Due date
G2:N2 = Job operations (example: 3-axis - Bend - BP - Black Zinc)

What I need this formula to do is to ignore any cells that are filled with any color. This is because as operations are completed, I fill that operations cell with a color.

I know excel cant check things by cell color, but if I can do something by making a VBA command, that works for me. Thanks!

 

[Phantasm]

Heres a link to the file:

http://s000.tinyupload.com/?file_id=83741350222711399305

I was doing test calculations in columns X, Y, & Z. If you look at just the Y2, the value should be 0 if the ignore colored cells is working. I should be 2.5 if the ignore colored cells is not working, but the CASE statements are working.

 

[RoryA]

Either use a file sharing site if really necessary, or post screenshots. It is relatively rare to need the actual workbook.

 

[Phantasm]

Either use a file sharing site if really necessary, or post screenshots. It is relatively rare to need the actual workbook.

Done. I posted the exact function & it worked for her, but not for me. I believe the best next step would be to post the workbook. If Im wrong, I apologize.

 

[becci.gott]

Right... this is going to sound completely nuts, but I think that it is because of the font when you copy and paste!

In the code at the moment, the case options have are surrounded by speech marks that look like this: “”

But, they should have speech marks that look like this: " "

I did a find and replace in the VBA function, and as soon as they were changed, it worked absolutely fine.

 

[becci.gott]

(Also, in the case statement, you have MILL listed twice)

 

[Phantasm]

Right... this is going to sound completely nuts, but I think that it is because of the font when you copy and paste!

In the code at the moment, the case options have are surrounded by speech marks that look like this: “”

But, they should have speech marks that look like this: " "

I did a find and replace in the VBA function, and as soon as they were changed, it worked absolutely fine.

Wow, that is crazy...See Rory, I would have never figured this out without me sending her the file. It works great now. How did you manage to notice that? Thanks a million! I owe you one!

 

[becci.gott]

Sheer luck to be honest.. I was stepping through the code and couldn't quite figure out how it was applying values to empty cells.. Got there in the end though!