Excel 2007: How to Format Cells Based on Comparison of Multiple Cells in Other Rows

I have a spreadsheet that lists items in multiple customers purchase history that looks something like this: Example of my spreadsheet
If an item does not have the same price for all customers I need the price cell formatted like seen here: Example of how I'd like it to formatSo if ItemCode is the same in other rows but the CurrentPrice is different in another row I need the price highlighted.

Can someone direct me on how to accomplish this?

Note, in the real world there may be more or less than 3 customers with the same item so I can't expect there will always be 3 customers in the same order as in the example.
Who is Participating?
Martin LissOlder than dirtCommented:
Here's a workbook with the code. Type Ctrl+Shift+D to run the macro,
Martin LissOlder than dirtCommented:
You can use this macro.
Sub CheckPrices()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strItem As String
Dim lngStart As Long
Dim bDifference As Boolean
lngLastRow = Range("A1048576").End(xlUp).Row

strItem = Range("B2")
lngStart = 2
For lngRow = 3 To lngLastRow + 1
    If Cells(lngRow, "B") <> Cells(lngRow - 1, "B") Then
        If bDifference Then
            Range(Cells(lngStart, "C"), Cells(lngRow - 1, "C")).Style = "Bad"
            bDifference = False
        End If
        lngStart = lngRow
        If Cells(lngRow, "C") <> Cells(lngRow - 1, "C") Then
            bDifference = True
        End If
    End If
End Sub

Open in new window

ShumsDistinguished Expert - 2017Commented:
Hi Danny,

Sample/Dummy workbook would be helpful.

Assuming as above example, enter below formula in Helper Column (D):
=IF(OR(COUNTIF($A$2:$A$12,$A2)<1,($B2&$C2)=($B3&$C3),($B2&$C2)=($B1&$C1)),"Same Price","Highlight")

Open in new window

Change the range accordingly

  1. Select C2:C12
  2. Navigate to Home/Conditional Formatting
  3. Click on New Rule
  4. Select "Use a formula to determine which cells to format"
  5. In Format values where this formula is true: =D2="Highlight"
  6. Format as needed
  7. Apply to C2:C12
See the screenshot below:
Highlight CellsHope this helps.

you could use Conditional formatting with a formula like this

Open in new window

EDIT corrected separator

ShumsDistinguished Expert - 2017Commented:
No Comments been added further
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.