Solved

Highlight Data if not matched

Posted on 2011-03-22
5
410 Views
Last Modified: 2012-05-11
Hi Experts,

I would like to request Experts help create a macro to highlight data (cell) at column “Number” if data at column “Type1” and “Type2” having a different value. I have manually highlighted few data at Data sheet for Experts to get better idea. Hope Experts will help me to create this feature.



CompareData.xls
0
Comment
Question by:Cartillo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 450 total points
ID: 35195792
Try this...
Sub ColorNumberT1neT2()
Dim myCols As Range
Dim myCell As Range

    For Each myCols In Range("A1", Range("A1").End(xlToRight))
    
        If myCols.Value = "Number" Then
        
            For Each myCell In Range(Cells(2, myCols.Column), Cells(Rows.Count, myCols.Column).End(xlUp))
                If myCell.Offset(0, 2).Value <> myCell.Offset(0, 3).Value Then
                    myCell.Interior.Color = 255
                Else
                    myCell.Interior.Color = -4142
                End If
            Next myCell
        End If
    Next myCols
End Sub

Open in new window


it goes through the used columns finding "Number", then down each row comparing type 1 and type 2 which are offset from that number column 2 and 3 columns over, respectively.  if they're the same, they're colored red with the interior.colorindex property, else the fill is cleared.

Dave
CompareData-r1.xls
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35195837
Isn't this a conditional formatting question? :)

Dave
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 450 total points
ID: 35196070
:) - it could be.  I thought of that and did it (I'll post here just for coverage).  But, I wrote the code faster than I did the formatting due to lack of practice...

Here it is with conditional formatting.  Just setup the condition on the first column like this, then you can copy/paste formats on the all the other number columns...

I did it for row 2 to 1000...

This example with conditional formatting, so now you have both approaches.

Thanks for the "nudge" Dave.  Now I've done conditional formatting about 5 times (I need to do it 500 to get it, lol)

Cheers,

Dave
Picture1.png
CompareData-w-cond-formats-r1.xls
0
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 50 total points
ID: 35196109
Yes it does sound like a conditional formatting problem.
This link has a good tutorial for excel 2003 and earlier
http://www.cpearson.com/excel/cformatting.htm

and here is one for 2007+
http://www.howtogeek.com/howto/microsoft-office/using-conditional-cell-formatting-in-excel-2007/

Michael
0
 

Author Closing Comment

by:Cartillo
ID: 35199758
Hi Dave/Michael/Dave

Thanks a lot for the double solutions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question