#VBA, Conditional Formation using spreadsheet name from a cell value

[CincyExcel1]

I started using VBA in excel

I have a spreadsheet named "MasterReport"
1. this macro will create a new spreadsheet and name it with the value in MasterReport's cell "B2" (OldDatax)
2. the existing data will be copied and transferred to the "oldDatax" sheet everytime I run the macro
3. Fresh data will be pasted in the "MasterReport" and
4. I want to create a conditional formatting to highlight the changes (difference between the 2 spread sheets ("MasterReport" and Newly created spreadsheet.

Because I want to maintain the Old records, my idea was to simply update the name of MasterReport's cell "B2" every time I want to "archive the existing values in MasterReport, then the name of the new spreadsheet will be changing, that is why I want name the new sheet from MasterReport's cell "B2

it seems to be working until I tried to implement the formula for the conditional formatting Formula1:="=A5<> msOld !A5" but it is not working.

Any suggestions on how to fix this?

Here is the code:

Sub Macro1()
'
' Macro1 Macro
'
Dim MySheet As String, wsOld As Worksheet


Sheets("MasterReport").Select
Sheets.Add(After:=Sheets("MasterReport")).Name = Range("B2")
'Sheets.Add(After:=Sheets("MasterReport")).Name = "SheetName"

MySheet = Sheets("MasterReport").Range("B2").Value
Set wsOld = Sheets(MySheet)

Worksheets("MasterReport").Activate
Range("MasterTable[#All]").Copy
wsOld.Range("A4").PasteSpecial Paste:=xlPasteValues 'This line NEEDS to be in a "solo" line separate from the above or VBA "gets confused"

wsOld.Activate
Range("A:A,H:H,J:J,K:K,O:O,Q:Q").Select
Selection.NumberFormat = "m/d/yyyy"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Sheets("MasterReport").Activate
Range("MasterTable").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A5<> ' wsOld' !A5"


Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[6StringJazzer]

it seems to be working until I tried to implement the formula for the conditional formatting Formula1:="=A5<> msOld !A5" but it is not working.

What does "not working" mean? What are you expecting, and what happens instead?

You have extra spaces in this line of code. This will cause syntax errors. Maybe if you fix that it will work:

Change

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A5<> ' wsOld' !A5"

to

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A5<>'wsOld'!A5"

 

[CincyExcel1]

@ 6StringJazzer,
thank you for your reply. I copied paste your suggestion and VBA does like that either. (I made sure no extra spaces were left)
Any other suggestions?

 

[Alex Blakenburg]

You are using wsOld in a string. There is not such sheet name in Excel.
You will need to concatenate the formula string either using wsOld.Name or MySheet or the B2 value

 

[Alex Blakenburg]

Example in case you need it.

    MySheet = Sheets("MasterReport").Range("B2").Value
    Set wsOld = Sheets(MySheet)
    
    Dim sFormula As String
    sFormula = "=A5<>'" & wsOld.Name & "'!A5" ' wsOld.Name could also be MySheet or Sheets("MasterReport").Range("B2").Value
   
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:=sFormula

 

[CincyExcel1]

Alex, I tried to concatenate the formula like this,
Formula1:="=A5<>&wsold.name&!A5"

What am I missing?

 

[Alex Blakenburg]

Just compare it character by character to what I have in my post #5

 

[CincyExcel1]

Alex,
THANK YOU. If work perfectly

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[6StringJazzer]

Nice. I did not pick up that wsOld was an object name; I just (wrongly) assumed it was the actual sheet name.