Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Highlight Data if not matched

Posted on 2011-03-22
5
Medium Priority
?
416 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 42

Accepted Solution

by:
dlmille earned 1800 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 1800 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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

916 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