Issues with code to highlight cells

[bdmichael09]

Hello all,

Long story short, I have a piece of code here that is supposed to compare two experts in a head to head style on who has more clients. Whoever has the higher number, the code should highlight the cell with the higher total. If they are tied, it then compares a second category, pace %, as a tie breaker. Whoever has the higher % in this 2nd category, the code should then highlight the client number for the correct expert. In the case that both of these result in a tie, there is a second tie breaker that looks up an incomplete % value for each expert and whoever has the lower incomplete %, the client cell for the appropriate expert should be highlighted. If I ignore the 2nd tie breaker and take out the vlookup stuff going on, code works great. The vlookup code to compare experts just on the 2nd tie breaker by itself works great. In conjunction, the code highlights improper cells. For the life of me, I can't really understand why. Any help is appreciated. The first table below is the expert matchup table. The 2nd table is the table with their incomplete %. Sorry for the format of the tables, I've never really known how to insert tables into a forum post. (Also fyi, in my actual spreadsheet there is a hidden column on the right side so it looks wrong that I'm offsetting by 5 cells, but that is correct)

[TABLE="width: 790"]
<tbody>[TR]
[TD]Pace %
[/TD]
[TD]Expert
[/TD]
[TD="colspan: 2"]Client Folders
[/TD]
[TD]Client Folders
[/TD]
[TD]Expert
[/TD]
[TD]Pace %
[/TD]
[/TR]
[TR]
[TD]9.09%
[/TD]
[TD]Daniel Burger
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Kate Romeo
[/TD]
[TD]0.00%
[/TD]
[/TR]
[TR]
[TD]0.00%
[/TD]
[TD]Tricia Williams
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Deborah Barrett
[/TD]
[TD]5.00%
[/TD]
[/TR]
[TR]
[TD]5.00%
[/TD]
[TD]Elyssa Warren
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Elie Yaacoub
[/TD]
[TD]4.17%
[/TD]
[/TR]
[TR]
[TD]0.00%
[/TD]
[TD]Maggie Herrinton-Cox
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Jordan Davenport
[/TD]
[TD]3.70%
[/TD]
[/TR]
[TR]
[TD]8.33%
[/TD]
[TD]Sarah Stockman
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Jess Hoppe
[/TD]
[TD]5.56%
[/TD]
[/TR]
[TR]
[TD]0.00%
[/TD]
[TD]Joshua Kaufman
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Stacey Hochkins
[/TD]
[TD]3.70%
[/TD]
[/TR]
[TR]
[TD]0.00%
[/TD]
[TD]Erin Burke
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Patrick Thomas
[/TD]
[TD]0.00%
[/TD]
[/TR]
[TR]
[TD]0.00%
[/TD]
[TD]Amelia DeVoy
[/TD]
[TD][/TD]
[TD]vs
[/TD]
[TD][/TD]
[TD]Daniella Murcko
[/TD]
[TD]0.00%
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 430"]
<tbody>[TR]
[TD]Expert
[/TD]
[TD]Current Points
[/TD]
[TD] MTD Incomplete %
[/TD]
[/TR]
[TR]
[TD]Erin Burke
[/TD]
[TD]3
[/TD]
[TD]13.51%
[/TD]
[/TR]
[TR]
[TD]Maggie Herrinton-Cox
[/TD]
[TD]3
[/TD]
[TD]0.00%
[/TD]
[/TR]
[TR]
[TD]Jordan Davenport
[/TD]
[TD]2
[/TD]
[TD]2.44%
[/TD]
[/TR]
[TR]
[TD]Daniella Murcko
[/TD]
[TD]2
[/TD]
[TD]17.65%
[/TD]
[/TR]
[TR]
[TD]Patrick Thomas
[/TD]
[TD]2
[/TD]
[TD]13.33%
[/TD]
[/TR]
[TR]
[TD]Deborah Barrett
[/TD]
[TD]2
[/TD]
[TD]12.90%
[/TD]
[/TR]
[TR]
[TD]Elyssa Warren
[/TD]
[TD]2
[/TD]
[TD]4.00%
[/TD]
[/TR]
[TR]
[TD]Amelia DeVoy
[/TD]
[TD]2
[/TD]
[TD]24.14%
[/TD]
[/TR]
[TR]
[TD]Stacey Hochkins
[/TD]
[TD]1
[/TD]
[TD]12.50%
[/TD]
[/TR]
[TR]
[TD]Daniel Burger
[/TD]
[TD]1
[/TD]
[TD]0.00%
[/TD]
[/TR]
[TR]
[TD]Kate Romeo
[/TD]
[TD]1
[/TD]
[TD]16.28%
[/TD]
[/TR]
[TR]
[TD]Joshua Kaufman
[/TD]
[TD][/TD]
[TD]12.50%
[/TD]
[/TR]
[TR]
[TD]Sarah Stockman
[/TD]
[TD][/TD]
[TD]7.41%
[/TD]
[/TR]
[TR]
[TD]Tricia Williams
[/TD]
[TD][/TD]
[TD]12.50%
[/TD]
[/TR]
[TR]
[TD]Elie Yaacoub
[/TD]
[TD][/TD]
[TD]21.21%
[/TD]
[/TR]
[TR]
[TD]Jess Hoppe
[/TD]
[TD][/TD]
[TD]12.50%
[/TD]
[/TR]
</tbody>[/TABLE]

Sub formatwinnersrefi()
Dim counter As Integer, p1 As Single, p2 As Single
counter = Range("i6", Range("i6").End(xlDown)).Rows.Count
Range("i6").Select
Do Until counter = 0
p1 = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, -1), Range("P:R"), 3, 0)
p2 = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, 3), Range("P:R"), 3, 0)
If ActiveCell.Value > ActiveCell.Offset(, 2) Then
    ActiveCell.Interior.Color = RGB(113, 255, 113)
ElseIf ActiveCell.Value = ActiveCell.Offset(, 2) _
    And ActiveCell.Offset(, -2) > ActiveCell.Offset(, 5) Then
    ActiveCell.Interior.Color = RGB(113, 255, 113)
ElseIf p1 < p2 Then
    ActiveCell.Interior.Color = RGB(113, 255, 113)
Else: ActiveCell.Offset(, 2).Interior.Color = RGB(113, 255, 113)
End If
counter = counter - 1
ActiveCell.Offset(1, 0).Select
Loop
    
 
End Sub

 

[oldbrewer]

have you hidden the client folders numbers ?

 

[bdmichael09]

No, the cells are formulas in my spreadsheet. I assume that is why nothing copied over. When I copied the cells they are all 0s.

 

[bdmichael09]

bump

 

[oldbrewer]

where is the number of clients ?