Updating values and highlight them based on external excel file

Marciboy

New Member
Joined
Dec 28, 2019
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
Hey guys

I have two versions of the same files. One of them is the updated one with the new data. Now I have troubles writing a macro which does the following:

Ask which file I want to compare it to.
Comparing two identification numbers in those two different files.
If they match, it shall compare the price (which is in the same row as the identification number) and update + mark it if different and do nothing if the same.
go threw that list of identification numbers and search the next one in the other file and do that till there are no more rows

Is that something you guys can help me with?

Thanks a lot
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Marciboy

New Member
Joined
Dec 28, 2019
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
I do have this macro which just highlights the matches from Column 6 in the original file which are compared to the values in Column 1 in the new file. So now I need it to compare the values in the row where a match was found but now in column 10 from the original file to the one in Column 2 from the new file and changing those in the original file and making them yellow or bold if different. If they are the same just go on.

VBA Code:
Sub HighlightMatches()
    Application.ScreenUpdating = False
    
    Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean
       
       'Set up count as the number of rows filled in sixth column of first sheet.
       iRowL = Cells(Rows.Count, 6).End(xlUp).Row
       
       'Cycle through all cells in that column:
       For iRow = 1 To iRowL
          'For every cell with sth. in it, search through the first column in each worksheet in the workbook for a value that matches that cell value.

          If Not IsEmpty(Cells(iRow, 6)) Then
             For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
                bln = False
                var = Application.Match(Cells(iRow, 6).Value, Worksheets(iSheet).Columns(1), 0)
                
                'If matching value, indicate success by setting bln to true and exit the loop;
                'otherwise, continue searching until you reach the end of the workbook.
                If Not IsError(var) Then
                   bln = True
                   Exit For
                End If
             Next iSheet
          End If
          
          'If you do not find a matching value, do not bold the value in the original list;
          'if you do find a value, bold it.
          If bln = False Then
             Cells(iRow, 6).Font.Bold = False
             Else
             Cells(iRow, 6).Font.Bold = True
          End If
       Next iRow
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,760
Members
418,411
Latest member
Excellency

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
Top