UsedRange in all worksheets Wb1 to find matching value in Wb2.

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
58
I'm trying to get workbook 1, all sheets used range, if cell value = a cell value in column a of sheets"Final" in Wb2. if so color cell indexcolor 6. the following cod runs but no color change.
Code:
Sub color_matching_between_workbooks()
    Dim sh As Worksheet
    'Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb2 = Workbooks.Open("C:\Users\Desktop\17057 System Line List.xlsm")
    Dim finalrow As Integer
    Dim i As Integer
    finalrow = wb2.Sheets("Final").Range("A6000").End(xlUp).Row
    For i = 2 To finalrow
    
    
    For Each sh In ThisWorkbook.Worksheets
Dim cell As Range


For Each cell In sh.usedrange
If cell.Value = i Then
cell.Interior.ColorIndex = 4
End If


Next cell
Next sh
    
    Next i
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,504
Try:
Code:
Sub color_matching_between_workbooks()
    Application.ScreenUpdating = False
    Dim sh As Worksheet, srcWS As Worksheet, wb1 As Workbook, wb2 As Workbook, rng As Range, fnd As Range
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("C:\Users\Desktop\17057 System Line List.xlsm")
    Set srcWS = Sheets("Final")
    For Each sh In wb1.Sheets
        For Each rng In sh.UsedRange
            Set fnd = srcWS.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                rng.Interior.ColorIndex = 6
            End If
        Next rng
    Next sh
    Application.ScreenUpdating = True
End Sub
 

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
58
Mumps, ran into small issue, notice that it colors some random ranges. i double check that the cells are clear. i can not figure it out.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,504
Try this version of the macro. It will clear any existing color fill before starting the search each time you run the macro.
Code:
Sub color_matching_between_workbooks()
    Application.ScreenUpdating = False
    Dim sh As Worksheet, srcWS As Worksheet, wb1 As Workbook, wb2 As Workbook, rng As Range, fnd As Range
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("C:\Users\Desktop\17057 System Line List.xlsm")
    Set srcWS = Sheets("Final")
    For Each sh In wb1.Sheets
        sh.UsedRange.Cells.Interior.ColorIndex = xlNone
        For Each rng In sh.UsedRange
            Set fnd = srcWS.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                rng.Interior.ColorIndex = 6
            End If
        Next rng
    Next sh
    Application.ScreenUpdating = True
End Sub
 

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
58
still the same issue. So this may help. i have 30 tabs that this code look at. so on the first tab is where it is coloring in unused cells. ("A1:N54") the is nothing in these cells. i cleared the cells to be sure. and the funny part is the longer the code runs, the wider the range gets. color sets in at end of code.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,504
Are you saying that the problem is that the macro is coloring blank cells otherwise it works properly?
 

Forum statistics

Threads
1,081,468
Messages
5,358,862
Members
400,514
Latest member
JoHio2577

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top