VBA - Highlight Value That Doesnt Match Top Value

[vba_monkey]

Please let me know if this doesnt make sense.

This is a simplified version of a table of insurance policies that I have and I need to go through the values in the third column and highlight commision rates that differ from the first value of each policy.

For example, any endorsement for policy P1 that has a commission rate that isnt 90% should be highlighted in red.

[TABLE="width: 280"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Policy No.[/TD]
[TD]Endorsement No.[/TD]
[TD]Commission Share
[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]E1[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E2[/TD]
[TD="align: right"]86%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E3[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E4[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E5[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]E1[/TD]
[TD="align: right"]95%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E2[/TD]
[TD="align: right"]90%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E3[/TD]
[TD="align: right"]95%[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]E1[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E2[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E3[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E4[/TD]
[TD="align: right"]85%
[/TD]
[/TR]
</tbody>[/TABLE]


Values in this table that should show as red are P1,E2 (86%), P2,E2(90%) and P3,E3 (80%).

Any help will be greatly appreciated. Thanks.

 

[Special-K99]

Am no VBA expert but this works

Sub HighLiteNotTop()
Application.ScreenUpdating = False
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
lastpol = ""
For i = 2 To Lastrow
If Worksheets("Sheet1").Cells(i, 1) <> "" Then currpol = Cells(i, 1): lastpol = currpol: share = Cells(i, 3) Else
If Worksheets("Sheet1").Cells(i, 3) <> share Then Cells(i, 3).Interior.ColorIndex = 3

Next i
End Sub

 

[vba_monkey]

Perfect, thank you.