I have a Excel sheet shared by several users. We use vlookup
to enter updated data into the sheet, so all users know what has changed. We would like to have any cell that has changed be highlighted in yellow with red font.
I have this code that works, but i don't know how to add yellow fill to the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Boolean Application.ScreenUpdating = False 'Create Change Log if one does not exist.
i = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Change Log" Then
i = True
Exit For
End If
Next ws
If Not i Then Set ws2 = ThisWorkbook.Worksheets.Add
ws2.Visible = xlSheetHidden
ws2.Name = "Change Log"
ws2.Range("A1") = "Sheet"
ws2.Range("B1") = "Range"
ws2.Range("C1") = "Old Text Color"
Else Set ws2 = Sheets("Change Log")
End If 'Store previous color data in change log for rollback.
ws2.Range("A1").Offset(ws2.UsedRange.Rows.Count, 0) = Target.Worksheet.Name
ws2.Range("B1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Address
ws2.Range("C1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Font.Color 'Change font color to red.
Target.Font.Color = 255
Application.ScreenUpdating = True
End Sub