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!

 

[becci.gott]

I am on my phone now, so forgive me if this isn’t perfect, but it should get you most of the way there...

Public Function JobPriority(tgt As Range)

Dim t as double

For each cell in tgt

If cell.Interior.ColorIndex = xlNone Then

Select case ucase(cell.value)

Case “SAW”
t = t + 0.5
Case “3-AXIS”
t = t + 1
Case “MILL”
t = t + 1
end select

Else
‘Do nothing
End If

Next cell

JobPriority = t

End Function

Then in your worksheet you can do something like =today()-JobPriority(G2:N2)

Obviously you can add all the other values into the case statement (making sure they are in uppercase as the select statement converts it to CAPS to make sure it isn’t case sensitive.

 

[Phantasm]

I am on my phone now, so forgive me if this isn’t perfect, but it should get you most of the way there...

Public Function JobPriority(tgt As Range)

Dim t as double

For each cell in tgt

If cell.Interior.ColorIndex = xlNone Then

Select case ucase(cell.value)

Case “SAW”
t = t + 0.5
Case “3-AXIS”
t = t + 1
Case “MILL”
t = t + 1
end select

Else
‘Do nothing
End If

Next cell

JobPriority = t

End Function

Then in your worksheet you can do something like =today()-JobPriority(G2:N2)

Obviously you can add all the other values into the case statement (making sure they are in uppercase as the select statement converts it to CAPS to make sure it isn’t case sensitive.

Ive adapted this, but it still will not disregard cells that are filled with color. This is what I have:

Public Function JobPriority(tgt As Range)

Dim t As Double

For Each cell In tgt

If cell.Interior.ColorIndex = xlNone Then

Select Case UCase(cell.Value)

Case “SAW”
t = t + 0.5
Case “3_AXIS”
t = t + 1
Case “MILL”
t = t + 1
Case “BP”
t = t + 1
Case “MILL”
t = t + 1
Case “BLANCHARD”
t = t + 5
Case “RAL * ”
t = t + 5
Case “HARDEN”
t = t + 2
Case “ASSEMBLY”
t = t + 0.5
Case “BLACK_ZINC”
t = t + 1
Case “BRIGHT_ZINC”
t = t + 1
Case “BEND”
t = t + 3
Case “BLACK_ANODIZE”
t = t + 5
Case “KEYSLOT”
t = t + 5
Case “SPLINE”
t = t + 5
Case “SAND”
t = t + 0.5
Case “HELICOIL”
t = t + 0.2
Case “CLEAR_ANODIZE”
t = t + 1
Case “WILLIAMS”
t = t + 5
Case “WELD”
t = t + 2
Case “LATHE”
t = t + 1
Case “EPI”
t = t + 5
End Select


Else
'Do Nothing
End If

Next cell

JobPriority = t

End Function

 

[becci.gott]

I have just checked it, and it is working for me. You do have to recalculate the worksheet if you apply colour to a cell to kick off the function.

The only other thing that I can think of if it has something to do with the colour that you are using.. so you might be able to change this part if cell.Interior.ColorIndex = xlNone then with something like if cell.interior.color <> 255 then

If you are unsure of the colour of the cell that you are using, select the cell and then in the VBA window view the immediate window (click view and then immediate window) and type in the following: ?activecell.Interior.Color this will add a number below the command line when you hit return. Use this number in place of the 255 used above.

If that doesn't help, then I am stumped.

 

[Phantasm]

Still nothing. I'm using office 2016 it that makes any difference. I tried all different ways, so I'm not sure what the issue is. I will say that the counting it does seems to be off. I can send you a copy of my worksheet, if you would like to look at it.

 

[becci.gott]

You can email it if you like. I have a gmail address the same as my username (if that makes sense).

 

[Phantasm]

Just sent. Thanks for taking the time to look at it.

 

[becci.gott]

I haven't received the email - did you send it to gmail.com?

 

[RoryA]

Please note that using email is against the board policy.

 

[becci.gott]

Apologies - I didn't realise that. How would the OP be able to transfer the file for me to look at?

 

[RoryA]

They can put it on a file hosting site and add a link here. It would also help to have more specific information than "the counting it does seems to be off".