VBA Lookup???

[zebra99]

I have the following code that I need to modify to count the cell only if it is 3 or above and the value of the cell would be value -2. Maybe some kind of lookup function with the following table?

0 0
1 0
2 0
3 1
4 2
5 3
6 4

This modified value would be for all cells in the offset. Then when the value of the original cell is greater than 4 the offset becomes 1 to 6.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("H3:IV20") '
If oCell.Value <> "" Then '
myTot = oCell.Value
For i = 1 To 2
myTot = myTot + oCell.Offset(0, -i).Value
Next i
Select Case myTot
Case Is < 3
oCell.Interior.ColorIndex = xlNone
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 45
Case Is = 5
oCell.Interior.ColorIndex = 3
Case Is > 5
oCell.Interior.ColorIndex = 39
End Select '
End If '
Next oCell
End Sub

 

[SkipVought]

Huh?

Clear as mud!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zebra99]

OK, I will start with the beginning part. This is for a tardy policy at work. The cell will have entries from blank to 20 or so (the number of tardies that month for an individual) but the employee is given 2 freebies a month. No action is taken until 3 or more have accumulated. In addition it looks back at the previous 2 months following the same parameters of 2 freebies and will do an accumulation over those 3 months following 4 steps of diciplinary action, on the 4th it looks back at the previous 5 months.

 

[SkipVought]

Just pretend that I don't know ANYTHING about your project.

Just pretend that I don't know ANYTHING about the data that represents, "the beginning part" or ANY part, for that matter.

Please lead me by the hand. Show me a sample of the data that you are working with and how that corresponds to the two columns of numbers you originally posted. And from that sample, what you expect the result to be.

Please be painfully detailed, as I still am completely in the dark!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zebra99]

Sorry Skip,

Let me start by saying I appreciate your help. By providing you this detailed analysis I found I was trying to build it incorrectly, thank you for that! This should clear up the confusion, if you have any follow-up questions please ask.

I attached sample data at

http://members.cox.net/bbelsky1/Sample Data.xls

I would like the cell to highlight yellow if a person hit 3 within a month. If they hit 4 in the same month it would highlight light orange, 5 would be red, 6 and above would be lavender.

Once a person highlights yellow or orange it would “observe” for 3 months, if they hit 3 in any of the 3 months following the highlighted month it would ignore the above color scheme and automatically go to the next color, but if in that same month they hit 4 it would then follow the color scheme and advance to the next color.

For an example let's say that Person9 highlighted yellow in Jan, then within 3 months hit 3 again, Feb for instance. In Feb the color would be orange, then within 3 months of Feb hit 3 again, say in Apr. The color would then go to red in Apr, once they have been highlighted red I would like it to “observe” the following 6 months instead of 3 months. If they hit 3 again within 6 months of Apr they would go to lavender, say May in this case. Then if they were to hit 3 again within 6 months of May it would go to lavender again.

Yellow and orange would “observe” for 3 months. Red and lavender would “observe” for 6 months.

In addition, if they were to hit more than 3 in a single month colors could be skipped. For example Person4 had 3 in Jan for a yellow highlight then had 5 in Feb, 3 in Feb would have made them orange, 4 would be red, and 5 would be lavender. That person would then follow the 6 month rule of going to lavender again if 3 or more were hit and they did that in May.

I thought the lookup table may come into play here, but it may not. Originally I was thinking color was based solely on a count, but it is based on a count and previous color.

 

[SkipVought]

Instead of thinking of this as "based on a count and previous color", think of it as based on a SUM of cells.

The threshholds would change the RANGE of cells to SUM. Same principle.

Have at it!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zebra99]

I appreciate your confidence in my ability, but VB code is new to me. I have never written anything in VB and do not know where to even begin. Any help here with the code itself would be appreciated.