I am looking to automatically update comments on cells in one sheet and fill them with a specific color based on the values of another sheet.
The range is columns 1 through 140 and rows 2 through the last row.
If cell A2 on Sheet 2 changes, then cell A2 on Sheet 1 should have a comment created that has the value reflected in sheet 2 cell A2. This relationship extends to all cells in the range.
I started to tinker and this code will work for the single cell reference but needs to be altered to the range and does not work unless the changes are done manually on Sheet2. That's an issue because Sheet2 has formulas.
As an example, Sheet1 cell A2's is referenced in a formula in Sheet2 cells FA2 and FG2. Sheet2 cellA2 references those two cells. So if a change is made to Sheet 1 cell A2, it can indirectly trigger a value to populate in Sheet2 A2, which should be the comment displayed in Sheet1 A2. I tried to alter the code to delete the comment in the cell if Sheet 2 cell A2 formula result is null, but had issues. When I did some research on how to get around manual inputted changes being the sole driver of the comment additions, I came upon this thread:
excel - VBA code doesn't run when cell is changed by a formula - Stack Overflow
but I am not sure how to alter it to my needs. As for the color fill, I'd use conditional formatting, but there will be other users that won't use paste values at times. Ideally, if Sheet 2 cell A2 formula results have "missing" as part of the text string, then cell.Interior.ColorIndex = 4, if they have "invalid", then cell.Interior.ColorIndex = 3, and if they have "delete", then cell.Interior.ColorIndex = 26. Any help or a point in the right direction would be much appreciated.
The range is columns 1 through 140 and rows 2 through the last row.
If cell A2 on Sheet 2 changes, then cell A2 on Sheet 1 should have a comment created that has the value reflected in sheet 2 cell A2. This relationship extends to all cells in the range.
I started to tinker and this code will work for the single cell reference but needs to be altered to the range and does not work unless the changes are done manually on Sheet2. That's an issue because Sheet2 has formulas.
As an example, Sheet1 cell A2's is referenced in a formula in Sheet2 cells FA2 and FG2. Sheet2 cellA2 references those two cells. So if a change is made to Sheet 1 cell A2, it can indirectly trigger a value to populate in Sheet2 A2, which should be the comment displayed in Sheet1 A2. I tried to alter the code to delete the comment in the cell if Sheet 2 cell A2 formula result is null, but had issues. When I did some research on how to get around manual inputted changes being the sole driver of the comment additions, I came upon this thread:
excel - VBA code doesn't run when cell is changed by a formula - Stack Overflow
but I am not sure how to alter it to my needs. As for the color fill, I'd use conditional formatting, but there will be other users that won't use paste values at times. Ideally, if Sheet 2 cell A2 formula results have "missing" as part of the text string, then cell.Interior.ColorIndex = 4, if they have "invalid", then cell.Interior.ColorIndex = 3, and if they have "delete", then cell.Interior.ColorIndex = 26. Any help or a point in the right direction would be much appreciated.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sResult As String
If Union(Target, Worksheets("Sheet2").Range("A2")).Address = Target.Address Then
Application.EnableEvents = False
Application.ScreenUpdating = False
sResult = Target.Value
Target.ClearContents
With Worksheets("Sheet1").Range("A2")
.ClearComments
.AddComment
.Comment.Text Text:=sResult
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub