none
Check which cell in the sheet has changed. RRS feed

  • Question

  • With reference to this thread, is it possible to check which cell has been changed in content (either text decreased or increased or deleted) by changing the color of the cell to yellow, when loading data from userform to the sheet.

    The scenario is to load data from sheet to form, review the data, apply changes to the form, then load data back from form to sheet. Only these cells with value different than the old value of the cell on the sheet will be colored.

    This should not change the color of the cell when just double click the cell.

    Is this possible? Can you help me please?

    Thanks

    Wednesday, May 20, 2015 3:15 AM

Answers

  • I assume if a change has occurred previously and a color has been previously set but no change in the current edit that you want the color removed. If so, something like the following where you test for a change and if changed then set the Interior.Color of the cell else if no change set Interior.color to no color.

    I think that the code I have modified is from the latest upload of the file that I coded for you previously.

    Identify the RGB code for the color as follows.

    1. Select the required worksheet.
    2. Select the required range.
    3. Set the required fill color for the range (if not already set)
    4. While the range is still selected, Select the fill color icon.
    5. Select More colors.
    6. Select the Custom tab and you will see the RGB color references.

               

               If rngId.Offset(0, 1).Value <> Me.txtFamilyName.Value Then
                    rngId.Offset(0, 1).Interior.Color = RGB(255, 255, 0)
                    rngId.Offset(0, 1) = Me.txtFamilyName.Value
                    strChanges = strChanges & "Family Name, "
                Else
                    rngId.Offset(0, 1).Interior.Color = xlNone
                End If        
      


    Regards, OssieMac

    • Marked as answer by Ahmed Morsyy Thursday, May 21, 2015 4:06 AM
    Wednesday, May 20, 2015 4:48 AM

All replies

  • I assume if a change has occurred previously and a color has been previously set but no change in the current edit that you want the color removed. If so, something like the following where you test for a change and if changed then set the Interior.Color of the cell else if no change set Interior.color to no color.

    I think that the code I have modified is from the latest upload of the file that I coded for you previously.

    Identify the RGB code for the color as follows.

    1. Select the required worksheet.
    2. Select the required range.
    3. Set the required fill color for the range (if not already set)
    4. While the range is still selected, Select the fill color icon.
    5. Select More colors.
    6. Select the Custom tab and you will see the RGB color references.

               

               If rngId.Offset(0, 1).Value <> Me.txtFamilyName.Value Then
                    rngId.Offset(0, 1).Interior.Color = RGB(255, 255, 0)
                    rngId.Offset(0, 1) = Me.txtFamilyName.Value
                    strChanges = strChanges & "Family Name, "
                Else
                    rngId.Offset(0, 1).Interior.Color = xlNone
                End If        
      


    Regards, OssieMac

    • Marked as answer by Ahmed Morsyy Thursday, May 21, 2015 4:06 AM
    Wednesday, May 20, 2015 4:48 AM
  • Hello OssieMac, 

    How are you?

    The code is working as required. This is awesome!.

    Thanks.

    Thursday, May 21, 2015 4:06 AM