Solved

Highlight Data if not matched

Posted on 2011-03-22
5
412 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:Michael Fowler
Michael Fowler 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

632 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