both need to refer to a static cell in order to do that, which mean you need to put the value of 0.85 to another cell, let both C13 in Sheet1 and Sheet2 refer to it.
28968462.xlsx
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count = 1 Then
If ActiveCell.Worksheet.Name = Target.Worksheet.Name Then
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
If Target.Address = Range("C13").Address Then
If Sh.Name = "Sheet1" Then
Sheets("Sheet2").Range("C13").Formula = Left(Sheets("Sheet2").Range("C13").Formula, InStr(Sheets("Sheet2").Range("C13").Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
ElseIf Sh.Name = "Sheet2" Then
Sheets("Sheet1").Range("C13").Formula = Left(Sheets("Sheet1").Range("C13").Formula, InStr(Sheets("Sheet1").Range("C13").Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
End If
End If
End If
End If
End If
End Sub
Change-constant-macro.xlsm
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count = 1 Then
If ActiveCell.Worksheet.Name = Target.Worksheet.Name Then
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
If Not Intersect(Target, Range("C13:I13")) Is Nothing Then
If Sh.Name = "Sheet1" Then
Sheets("Sheet2").Range(Target.Address).Formula = Left(Sheets("Sheet2").Range(Target.Address).Formula, InStr(Sheets("Sheet2").Range(Target.Address).Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
ElseIf Sh.Name = "Sheet2" Then
Sheets("Sheet1").Range(Target.Address).Formula = Left(Sheets("Sheet1").Range(Target.Address).Formula, InStr(Sheets("Sheet1").Range(Target.Address).Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
End If
End If
End If
End If
End If
End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
18 Experts available now in Live!