VBA Compare rows and highlight the difference
Hi,
Can anyone please help me write a VBA code to compare Cost for each SKU at different whse locations and highlight If there is any difference in the cost. Jump to the next SKU range and iterate the process.
Sample table is below:
SKUNum WhseLocation Cost
A132 1-10 1
A132 2-10 1
A132 3-10 1
A133 1-10 0.5
A133 2-10 0.5
A133 3-10 1
A134 1-10 2
A134 2-10 1
A134 3-10 0.5
You can highlight the values in the table by using Conditional formatting, with the formula option. Select your table starting with A2, and apply CF with your choice of color using the formula
=COUNTIF($A:$A,$A2)<>COUNTIFS($A:$A,$A2,$C:$C,$C2)
Bernie Deitrick
Excel MVP 2000-2010
Hi Bernie,
This doesn't work. It's not highlighting any of the cells where the cost differs for the same SKU.
You made a mistake, then, modifying my formula for your actual workbook. Here is a working example.
Thanks Bernie, this document was helpful but this highlights all the SKU's that was different cost at different locations.
Is there anyway It can highlight only the location which has different cost.
For Ex - In the sample table, For SKU A133, It should only highlight the row with location 3-10 which has different value than the other 2 locations for this SKU.
You could try the formula
=AND(COUNTIF($A:$A,$A2)<>COUNTIFS($A:$A,$A2,$C:$C,$C2),COUNTIFS($A:$A,$A2,$C:$C,$C2)=1)
It will work for A133, but will also highlight the three different values for A134 - unless you have some other logical requirement.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks