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]

I think I can use something like:

If Range("A1").Interior.Pattern <> xlNone Then

But Im not sure how to format it for the use I need.

 

[Phantasm]

Whats throwing me off is how do I reference the cells I need to evaluate...Like in the first line, the calculation is done in O2 with E2 & G2:N2 being the cells with the data. Then I need to do it for each row, so O3 evaluates E3 & G3:N3 and so on.

 

[Phantasm]

Can anyone help me on this?

 

[Phantasm]

Bump

 

[Phantasm]

Bump

 

[becci.gott]

You could create a function in VBA that would give you a TRUE/FALSE response to the colour and then use that in your formula...

i.e.

Public Function coloured_cell(tgt As Range)


If tgt.Interior.ColorIndex = xlNone Then
coloured_cell = False
Else
coloured_cell = True
End If


End Function

 

[Phantasm]

You could create a function in VBA that would give you a TRUE/FALSE response to the colour and then use that in your formula...

i.e.

Public Function coloured_cell(tgt As Range)


If tgt.Interior.ColorIndex = xlNone Then
coloured_cell = False
Else
coloured_cell = True
End If


End Function

But how would I get it to get the target cells? I need this calc at the end of each row. G2:N2 is the target for the first line and O2 is where this calculation would happen for that line. Then the next row would have G3:N3 for the target with O3 being the calculation?

 

[becci.gott]

If you copy the function code into a VBA module, it becomes a function (like any other function in the workbook).

In the workbook, you would need to type in

=coloured_cell(G2:N2)

This would give you a TRUE / FALSE if any of the cells have a coloured background. (it will return true even if only 1 cell is coloured).

For your example formula, you could wrap it in an IF function....

=if(coloured_cell(G2:N2)=TRUE,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})),"")

 

[Phantasm]

If you copy the function code into a VBA module, it becomes a function (like any other function in the workbook).

In the workbook, you would need to type in

=coloured_cell(G2:N2)

This would give you a TRUE / FALSE if any of the cells have a coloured background. (it will return true even if only 1 cell is coloured).

For your example formula, you could wrap it in an IF function....

=if(coloured_cell(G2:N2)=TRUE,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})),"")

Thats close, but I need to evaluate each cell seperately. If it sees 1 colored cell in the range, it stops. Is there a way I can code the counting to be done in the function? Something like:

If tgt.Interior.ColorIndex = xlNone Then
coloured_cell = False
add the value of all the non colored cells in the range with SAW = .5, 3-axis = 1...
Else
coloured_cell = True
all colored cell values = 0
End If