Finding Matching Numbers From Last Row Up

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Code:
'********** Step Through Rows Backwards *****************
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For myrow = lastrow To 6 Step -1
    Columns("B:C").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
        With Selection.FormatConditions(1).Font
            .Color = 0
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
    Selection.FormatConditions(1).StopIfTrue = False
Next myrow

Hello All.
I'm attempting to find matching numbers and highlight them in columns B&C. The code above I used the recorder, which is working, but does not seem like an efficient way. I feel if the numbers in column C extended a long way down, the code would take a long time to run, It appears this way, the program is running one line at a time, finds a matching number, and then starts all the way down at the bottom again to find another set of matching numbers.
Is there a better way to do this?
Thanks for the help
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Just get rid of the loop, it isn't actually doing anything.
 
Upvote 0
Ahh...I have a feeling I'll need the last row part later as the macro grows...It's just now beginning.
One thing, lets say a matching number in Column "C" is found, I'll then need to have that row, beginning in column C, to be colored to Column AZ. The current color is ColorIndex = 28.
For example, if C12 is found to match a number somewhere in Column B, then C12 will be colored 28, and C12:AZ12 will have the ColorIndex of 28.
Can you help me with that?
That will help me a lot. Thanks
 
Upvote 0
Fraid not, I haven't done much with the modern style of conditional formatting.
 
Upvote 0
Code:
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For myrow = lastrow To 7 Step -1
    If (Cells(myrow, "C") = Cells(myrow, "B")) Then
        Rows(myrow & ":" & myrow).Interior.ColorIndex = 28
    End If
Next myrow

So I now have this code, which is working, but only if the matching numbers are in the same row. Example if B8 = C8, then row 8 will color blue. The problem is what if the number in B8 = the number in C15, then the program will not work.
How do I make it so if any numbers match, in columns B and C, then the dominant number is in column C and that is what gets shaded?
Thanks
 
Upvote 0
Are the numbers in both Cols B & C unique?
If not do you want to highlight every Col C value that matches Col B (ie if C8, C13 & C20 all match B5)?
 
Upvote 0
If not do you want to highlight every Col C value that matches Col B (ie if C8, C13 & C20 all match B5)?

The numbers in Column B are unique
The numbers in Column C are unique (but not in relation to Column B)
In other words, The numbers in column C (which will be a far larger amount of numbers) will have a percentage which matches the numbers in Column B. So, the matching numbers in Column C (the dominant number) will highlight that row.
By doing that, the numbers left over in Column C, which are not highlighted, will allow me to "Key" on, because they will not be in the data set of Column B.
Sorry it's confusing, but I believe this is what you were asking. In your example, If C8 were to match B5, then row C8 would be highlighted.
Also, there will be only one set of matching numbers between row C and Row B.
In your example, C8, C13, & C20 could not all match B5, because there will always be only one pair of matching numbers between the two columns.
Thanks for the help
 
Last edited:
Upvote 0
The numbers in Column B are unique
The numbers in Column C are unique (but not in relation to Column B)
In that case how do you determine a match? Can you please post a dozen, or so, examples of what B & C look like
 
Upvote 0
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Column B[/TD]
[TD="align: center"]Column C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2227534[/TD]
[TD="align: center"]2222574[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2221552[/TD]
[TD="align: center"]2221764.01[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2222575.04[/TD]
[TD="align: center"]16034[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2222[/TD]
[TD="align: center"]22221783[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]16222[/TD]
[TD="align: center"]2222575.04[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]16034[/TD]
[TD="align: center"]16462[/TD]
[/TR]
</tbody>[/TABLE]

So, In this scenario, C3 would match B6, C5 would match B3.
C3 & C5 would be dominant; therefore, Rows C3 & C5 would become highlighted.
The numbers in Column B are unique for Column B, as there is no matching number in Column B, which is also true for Column C. However, there are matching numbers between Columns B & C.
Thanks for the help
 
Last edited:
Upvote 0
The rest of the un-highlighted numbers in Column C, would not be in the Data Set of Column B. Therefore, all un-highlighted numbers in remaining in Column C, would be evaluated.
Any remaining numbers in Column B, that did not find a match in Column C, would be discarded as I would not be interested in them...only the remaining numbers in Column C.

Thanks for the help
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top