VBA EXCEL: Add the possibility to Highlight cells when value is chaged to my Macro

Question

Icreated a VBA code to create a new workbook where to copy paste a range from another workbook. I now would like to add the possibility of highlight all the cells that might change in the newly created workbook.

This is my code:

Sub Copy_Value_To_New_Workbook()
'Step 1 Copy the data
    Sheets("ACTIVITY LOG").Range("A1:O10000").Copy
'Step 2 Create a new workbook
    Workbooks.Add
'Step 3 Paste the data
    ActiveSheet.Paste Destination:=Range("A1")
'Step 4 Turn off application alerts
    Application.DisplayAlerts = False
'Step 5 Save the newly created workbook
    ActiveWorkbook.SaveAs _
    Filename:="Activity_log_piano.xlsx"
'Step 6 Turn application alerts back on
    Application.DisplayAlerts = True
'Step 7 highlights cells when changed
End Sub

thank u for your help!!

What are cells that might change? Couldn't all cells theoretically change? — BruceWayne, Sep 25 '18 at 13:55
`activesheet.usedrange.interior.color=vbRed` it may be best using something like `set w=Workbooks.Add` then you can say `w.usedrange.....` make sure you're still referencing the correct sheet. — Nathan_Sav, Sep 25 '18 at 13:56
@BruceWayne yes, what i meant is that in the new workbook that i created through the code above, all the cells have to be highlighted if changed. hope now it is clearer — Federico, Sep 25 '18 at 13:59
You could use this type of method to instead compare one workbook to another https://stackoverflow.com/questions/5387929/vba-macro-to-compare-all-cells-of-two-excel-files — Marcucciboy2, Sep 25 '18 at 14:00

score 0 · Answer 1 · answered Sep 25 '18 at 16:26

You could add conditional formatting to the new workbook.
Your workbook will be blank when you first create it, so any value in any cell is a change.

You could improve the code by using a variable to store the workbook reference as @Nathan_sav said in his comment to the question.

Sub Test()

    Sheet1.Range("A1:O10000").Copy
    Workbooks.Add

    ActiveSheet.Paste Destination:=Range("A1")
    With ActiveSheet.Range("A1:O10000")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=A1<>"""""
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = -0.249946592608417
        End With
    End With

End Sub

VBA EXCEL: Add the possibility to Highlight cells when value is chaged to my Macro

1 Answers1