Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
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