Solved

Highlight Data if not matched

Posted on 2011-03-22
5
405 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
5 Comments
 
LVL 41

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now