Closed Thread
Results 1 to 6 of 6

Helppppp!

  1. #1
    Registered User
    Join Date
    07-24-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Exclamation Helppppp!

    Hi,

    I have a spreadsheet that I am currently working on for a project. This spreadsheet has a lot of data in, the task at hand is to put in some sort of macro/coding that automatically highlights a row of data, as it is updated in a different sheet on the same document.

    The are thousands of rows on this spreadsheet, hence highlighting the recently updated rows seems like the best solution.

    I am trying to compare on two separate worksheets conditional formatting isn't working.
    I found the following code which I am trying to use, however every time I try run it but Excel freezes up and closes.
    Would you be able to advise?

    Sub Compare()
    '
    ' Macro1 Macro
    '
    ' compare two different worksheets in the active workbook
    CompareWorksheets Worksheets("Day1"), Worksheets("Services_gap_report")
    End Sub

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim diffB As Boolean
    Dim r As Long, c As Integer, m As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim rptWB As Workbook, DiffCount As Long
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating the report..."
    Application.DisplayAlerts = True
    With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
    End With
    With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr2 Then maxR = lr2
    If maxC < lc2 Then maxC = lc2
    DiffCount = 0
    For c = 1 To maxC
    For i = 2 To lr2
    diffB = True
    Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
    For r = 2 To lr1
    cf1 = ""
    cf2 = ""
    On Error Resume Next
    cf1 = ws1.Cells(r, c).FormulaLocal
    cf2 = ws2.Cells(i, c).FormulaLocal
    On Error GoTo 0
    If cf1 = cf2 Then
    diffB = False
    Exit For
    End If
    Next r
    If diffB Then
    DiffCount = DiffCount + 1
    ws2.Cells(i,c).Font.Bold = True
    ws2.Cells(i, c).Interior.ColorIndex = 19
    End If
    Next i
    Next c
    Application.StatusBar = "Formatting the report..."
    'Columns("A:IV").ColumnWidth = 10
    m = maxR - DiffCount - 1
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & "cells contain different values!", vbInformation, _
    "Compare" & ws1.Name & "with" & ws2.Name
    End Sub

    Thank you in advance

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Helppppp!

    the same rqst?!
    http://www.excelforum.com/excel-prog...s-changed.html
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Registered User
    Join Date
    07-24-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Helppppp!

    Yes it's the same

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Helppppp!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Helppppp!

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Helppppp!

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    http://www.excelforum.com/excel-prog...s-changed.html

    Thread Closed.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] niet zichtbaar van afbeelding e.d. helppppp
    By GM in forum Excel General
    Replies: 7
    Last Post: 07-26-2006, 03:10 PM

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