Conditional Compare Needed/Sort by Color

[Jack58]

I have a Spreadsheet that I need to compare numbers against a second set of numbers on another Tab in the same spreadsheet, then highlight any numbers that Match.

Example.
In Column E (Item Number) (Compare Tab) I need to compare the number against the Item Number in the Tab ?Numbers increased by Star?, Item Number (Column A), then I need to highlight the Matching Number in Column E (Item Number, Compare Tab) in a Color, such as Green.


Thanks!


Jack

[Zack Barresse]

Hi Jack,

Would you be able to zip and post an example of your data?

[Jack58]

I have attached a scaled down version of the file I am using. Normally it is about 5000 lines long.


Hope this helps.

Also I have this posted at the following site with more examples,

http://www.mrexcel.com/board2/viewto...358&highlight=

Thanks Again!!!



Jack

[Zack Barresse]

So which one are you looking from, Compare? .. Looking into 'Numbers increased by stars'? And are you only wanting the numbers highlighted matched on both sheets?

[Jack58]

I am looking into 'Numbers increased by stars' and wanting to only highlight the numbers that match on the Compare Tab.


Thanks

[Zack Barresse]

Maybe something like this ...

Option Explicit
Sub CheckItemNumbersForJack()
    Dim cel As Range, rng As Range, tmp
    Set rng = Sheets("Compare").Range("E12", Sheets("Compare").Range("E65536").End(xlUp))
    With Sheets("Numbers increased by Star")
        For Each cel In rng
            Set tmp = .Range("A:A").Find(cel.Value, _
                lookat:=xlWhole, MatchCase:=True)
            If Not tmp Is Nothing Then
                cel.Interior.ColorIndex = 4
                tmp.Interior.ColorIndex = 4
            End If
        Next cel
    End With
End Sub

[Zack Barresse]

Jack,

If you want conditional formatting, this will work ...


Select your Numbers increased by Stars sheet, select the column with your numbers in it, and name the range 'Numbers' (Insert -> Name Define). Do the same for your Compare sheet, but name that range 'Compare'.

In your Numbers increased by Stars sheet, select column A (or your item number col), select conditional formatting and type this in the Formula Is ...

=(A1<>"")*(ISNUMBER(MATCH(A1,Compare,0)))

Now go to your Compare sheet and select the entire column of item numbers there. Again choose conditional formatting, Formula Is ...

=(E1<>"")*(ISNUMBER(MATCH(E1,Numbers,0)))

[Jack58]

Thank you SO much for the code, Works VERY well. One more question. I would like to be able to sort this spreadsheet by the Highlighted color, or to have the items that are being highlighted in Green in the above code to appear at the beginning of the Spreadsheet.

Is this possible.


Thanks Again!!!!



Jack

[Jack58]

I have a Large Spreadsheet that I am using the following formula on, Whick Firefytr gave to me,

Option Explicit 
Sub CheckItemNumbersForJack() 
    Dim cel As Range, rng As Range, tmp 
    Set rng = Sheets("Compare").Range("E12", Sheets("Compare").Range("E65536").End(xlUp)) 
    With Sheets("Numbers increased by Star") 
        For Each cel In rng 
            Set tmp = .Range("A:A").Find(cel.Value, _ 
            lookat:=xlWhole, MatchCase:=True) 
            If Not tmp Is Nothing Then 
                cel.Interior.ColorIndex = 4 
                tmp.Interior.ColorIndex = 4 
            End If 
        Next cel 
    End With 
End Sub

I would like to be able to sort by the hightlighted color of the Cell, I am having a VERY hard time getting the attached file to do this. The file came to me from another department and I noticed that there is some soft of a Condtional format in the Spreadsheet.

I have tryed just about everything, such as Chips solution at http://www.cpearson.com/excel/SortByColor.htm

and someone on Mr. Excel with the following code,

Sub colorsort() 
Dim c As Range, rng 
Set rng = Range("A1:A" & Range("A65536").End(xlUp).Row) 
Range("A1").EntireColumn.Insert shift:=xlToRight 
For Each c In rng 
If c.Interior.ColorIndex = 35 Then 'depends on which green... (try 50 maybe?) 
    c.Offset(0, -1).Value = 1 
Else 
    c.Offset(0, -1).Value = 2 
End If 
Next c 
Cells.Sort key1:=Range("A2"), order1:=xlAscending, header:=xlGuess, MatchCase:=False 
Range("A1").EntireColumn.Delete shift:=xlToLeft 
End Sub

Hope someone can take a look at this problem and offer some type of soulution.


Thanks Again



Jack

[CBrine]

I have setup a User Defined function that returns the interior colorindex value. Just add this code to a module on your worksheet and enter the formula in a cell beside your list. Copy the formula down to the bottom of your data, and do a sort on the returned value. You should now have them sorted by color.

Example
GetColor("A1") Will return the colorindex value for cell A1.

HTH
Cal

PS- This formula will not work with conditional formatting.

Public Function GetColor(R As Range)
GetColor = R.Interior.ColorIndex
End Function

[mdmackillop]

Hi,
There is a KB item here to return conditional format colours.
MD

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

[Zack Barresse]

Jack,

Change this line ...

If c.Interior.ColorIndex = 35 Then 'depends on which green... (try 50 maybe?)

to this ...

If c.Interior.ColorIndex = 4 Then 'depends on which green... (try 50 maybe?)

Please note that this is using the Offset method, so you must have a blank column directly to the Left of the column in your 'rng'.

Edit: Recind comment about Offset. Oops.

[Jack58]

I Inserted a Column and Entered the Following formula into Cell A12, =GetColor("D10"),

It Returns #VALUE!

Please let me know what I am not doing correctly.


Thank you



Jack

[Zack Barresse]

Jack,

If the color is made by Conditional Formatting, it will not have a traditional ColorIndex number. You'd need to use something like the link MD posted.

I take it you went with the conditional formatting route with this one then?

[Jack58]

I only want to Display the Rows that are being highlighted by the Formula you wrote for me, so I do not have to go through the entire spreadsheet to view them.

Jack

[Jack58]

I did this,

Created a new Spradsheet, Insertd your code in a Module. When to Cell A1 and Highlighted it Red. Placed GetColor("A1") in Cell B2 and received a #VALUE! Error.

Please Help!

[Zack Barresse]

Ok, it's returning the error because you are using Conditional Formatting (CF), instead of traditional colors. You can tweak the macro I wrote to do all that highlighting for you, if you'd like.

[Jack58]

If you could do that for me that would be GREAT, again saving me a lot of time and work, and saving some of my hair I am ripping out over this project.


Jack

[Zack Barresse]

Well in the interim if you used the code posted by MD from the KB, you could use this formula in column E ...

=IF(ConditionalColor(D13,"interior")>=0,ConditionalColor(D13,"interior"),"")

And Jack, this workbook is huge. It takes forever to calculate. After this is done I highly suggest you find ways to streamline what you have in there already.

[Zack Barresse]

Okay Jack, This is the code I've added to a seperate module (which I named sort_Mod) ...

Option Explicit
Sub CheckItemNumbersForJack()
    Dim cel As Range, rng As Range, tmp
    Set rng = Sheets("Compare").Range("F12", Sheets("Compare").Range("F65536").End(xlUp))
    With Sheets("Numbers increased by Star")
        For Each cel In rng
            Set tmp = .Range("A:A").Find(cel.Value, _
            lookat:=xlWhole, MatchCase:=True)
            If Not tmp Is Nothing Then
                cel.Interior.ColorIndex = 4 'Green
                tmp.Interior.ColorIndex = 4 'Green
            End If
        Next cel
    End With
End Sub

Sub UnColorAllGreen()
    With Sheet1.Range("F12:F65536")
        .Interior.ColorIndex = 0
    End With
End Sub

Sub SortByGreen()
    Application.ScreenUpdating = False
    Dim c As Range, rng
    Set rng = Sheet1.Range("F12:F" & Range("F65536").End(xlUp).Row)
    For Each c In rng
        If c.Interior.ColorIndex = 4 Then 'depends on which green... (try 50 maybe?)
            c.Offset(, 15).Value = "Green"
        Else
            c.Offset(, 15).Value = ""
        End If
    Next c
    FilterMe
    Application.ScreenUpdating = True
End Sub

Sub FilterMe()
    Application.ScreenUpdating = False
    With Sheet1.Rows("11:65536")
        .AutoFilter
        .AutoFilter Field:=21, Criteria1:="Green"
    End With
    Application.ScreenUpdating = True
End Sub

Sub UnFilterMe()
    Application.ScreenUpdating = False
    If Sheet1.AutoFilterMode Then
        Sheet1.Cells.AutoFilter
    End If
    Application.ScreenUpdating = True
End Sub

Break down of Sub Routines ...

CheckItemNumbersForJack
This is basically the same routine we started with. I changed it to column F (and I hid column E) because that's where your item numbers were. This works the same as it did before.

UnColorAllGreen
This will basically reset the original macro (CheckItemNumbersForJack) as it will take away all of the Green color from column F. If you run this, you can't run the sort, as it will show nothing (no error handling as of yet).

SortByGreen
This will put "Green" in column U (an unused column) if the cell interior colorindex (not Conditional Format color - completely different) is green (colorindex of 4). This is what will be used to sort by. You can hide that column for asthetic purposes if you'd like.

FilterMe
This is the actual filter that you were asking for. This filters by the "Green" we put in there from the other Sub. Please Note: Some of the rows will have the yellow highlight and not look green. This is because the conditional formatting has over-ridden the inherent colorindex of the cell - it is only a 'face value' color, they are actually green, meaning they matched from your second sheet.

UnFilterMe
This will check if the AutoFilter is on, and if so, it will take it off. This Sub is the only one that has any 'real' kind of error handling in it.


Let me know how this works for you.