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.
dannyg280Asked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Here's a workbook with the code. Type Ctrl+Shift+D to run the macro,
29092736.xlsm
0
 
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
    Else
        If Cells(lngRow, "C") <> Cells(lngRow - 1, "C") Then
            bDifference = True
        End If
    End If
Next
End Sub

Open in new window

0
 
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.
Danny_Highlight-Cells.xlsx
0
 
Rgonzo1971Commented:
Hi,

you could use Conditional formatting with a formula like this
=STDEV.P(IF($B$2:$B$10=B2,$C$2:$C$10,""))<>0

Open in new window

EDIT corrected separator

Regards,
EE20180404.xlsx
0
 
ShumsDistinguished Expert - 2017Commented:
No Comments been added further
0
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.