# 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:
If an item does not have the same price for all customers I need the price cell formatted like seen here: So 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older 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
``````
0
Excel & VBA ExpertCommented:
Hi Danny,

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")
``````
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:
Hope this helps.
Danny_Highlight-Cells.xlsx
0
Older than dirtCommented:
Here's a workbook with the code. Type Ctrl+Shift+D to run the macro,
29092736.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Hi,

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

Regards,
EE20180404.xlsx
0
Excel & VBA ExpertCommented: