+ Reply to Thread
Results 1 to 5 of 5

Mark Cells if Match found in Sheet2

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-02-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    353

    Mark Cells if Match found in Sheet2

    Hi all,
    I have the below code that marks (turns bold and colored yellow) the Cells in Sheet1 (Column1) if there is a match on Sheet 2 (Column1).
    It is working okay but I need some help to tweak it a bit.

    Sub FindMatch()
        
        Dim i As Long, x, y
        With Sheets("Sheet1").[B5].CurrentRegion
            .Offset(1).Interior.ColorIndex = xlNone
            .Offset(1).Font.Bold = False
            For i = 2 To .Rows.Count
                x = Application.Match(.Cells(i, "a").Value, Sheets("Sheet2").Columns("A"), 0)
                If IsNumeric(x) Then
                    With .Rows(i)
                        .Interior.Color = vbYellow
                        .Font.Bold = True
    
                    End With
                End If
            Next
        End With
    
        
    End Sub

    Can you help me modify the code so it will loop and apply the same action to other columns in Sheet1?
    I have many columns on my actual sheet (around 400) but for the purpose of demo, I have only added 5 columns on my example spreadsheet.
    Sheet2 will not change, I only have 1 Column in this sheet (Only Column1)
    I hope you can help me out. Thank you in advance.
    Attached Files Attached Files
    Last edited by Andrew.Trevayne; 11-27-2018 at 02:02 AM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,336

    Re: Mark Cells if Match found in Sheet2

    Option Explicit
    
    Sub Flag()
        Dim i As Long, ii As Long, x
        Application.ScreenUpdating = False
        With Sheets("Sheet1").[B5].CurrentRegion
            .Offset(1).Interior.ColorIndex = xlNone
            .Offset(1).Font.Bold = False
            For ii = 1 To .Columns.Count
                For i = 2 To .Rows.Count
                    x = Application.Match(.Cells(i, ii).Value, Sheets("Sheet2").Columns("A"), 0)
                    If IsNumeric(x) Then
                        With .Cells(i, ii)
                            .Interior.Color = vbYellow
                            .Font.Bold = True
                         End With
                    End If
            Next i
        Next ii
        End With
        Application.ScreenUpdating = True
    End Sub
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,009

    Re: Mark Cells if Match found in Sheet2

    Question:
    1. Is there any formula in Sheet1?
    2. How many data rows do you have? I mean is it big?

  4. #4
    Forum Contributor
    Join Date
    11-02-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    353

    Re: Mark Cells if Match found in Sheet2

    Thank you sintek and Akuini. Amazing!

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,336

    Re: Mark Cells if Match found in Sheet2

    ..............................................
    Thanks.gif

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 09-24-2015, 08:20 AM
  2. Replies: 2
    Last Post: 10-22-2014, 11:32 PM
  3. [SOLVED] Match value in sheet1 for sheet2 and copy to sheet2
    By soli004 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 07:15 PM
  4. [SOLVED] If cell value on Sheet1 not found on Sheet2 copy row to Sheet2
    By Yxx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2013, 04:15 AM
  5. [SOLVED] Match one cell with another, if match found copy adjacent cells
    By Xiophoid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-07-2013, 05:50 AM
  6. Read Sheet1!A:A then mark corresponding cells in Sheet2
    By ashleysandell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2011, 07:06 AM
  7. Mark if cells match on 2 sheets
    By Optitron in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-03-2006, 09:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1