+ Reply to Thread
Results 1 to 4 of 4

VBA Code to Colour a Row as data is changed

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

    VBA Code to Colour a Row as data is changed

    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. However I am open to idea.

    Kind regards,
    Ish

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: VBA Code to Colour a Row as data is changed

    You can probably just use conditional formatting.

    It's hard to show without an example, but conditional formatting is a lot simpler than event macros (probably).

    Please upload a simple version of your worksheet(s)
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

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

    Re: VBA Code to Colour a Row as data is changed

    Thank you. Because 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 everytime 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

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

    Re: VBA Code to Colour a Row as data is changed

    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)

+ 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. [SOLVED] To compare two excel sheets and colour changed cells
    By ToyHor in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-27-2014, 05:00 PM
  2. Making cell changed colour, on input.
    By mikeuk1954 in forum Excel General
    Replies: 7
    Last Post: 05-10-2013, 07:38 AM
  3. Copy Data if Cell colour is changed
    By karobonas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2011, 07:41 AM
  4. Change colour of data when changed
    By Martin ©¿©¬ @REMOVETHIS.plus.com in forum Excel General
    Replies: 3
    Last Post: 05-13-2006, 05:45 AM
  5. [SOLVED] Format Cell Colour if contents are Changed ?
    By MAS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2006, 06:35 AM

Tags for this Thread

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