Solved

Highlight Data if not matched

Posted on 2011-03-22
5
407 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
round down to max number 11 32
Excel VBA 10 37
Best Excel  formula for  this scenario 2 33
Request to review costing formula 3 30
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

815 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

7 Experts available now in Live!

Get 1:1 Help Now