The best way is to use the Object properties of the worksheet. Here is the code I posted on another similar query. I hope it steers you in the right direction.
'This sets a date in another cell if there is an entry of the persons initials.
If Len(Trim(Target.Value)) >= 2 Then
If (Target.Offset(0, 22).Value) = "" Then
Target.Offset(0, 22).Value = Date
End If
End If
'Sets the colour value to use for each person.
If (Target.Value) = "BR" Then Color_value = 45
If (Target.Value) = "MK" Then Color_value = 15
If (Target.Value) = "VR" Then Color_value = 36
'Here the cells are formatted and the date entered.
Target.Offset(0, -1).NumberFormat = "dd/mm/yyyy"
Target.Offset(0, -1).Value = Date
Target.Offset(0, -1).EntireColumn.AutoFit
Target.Offset(0, 0).Interior.ColorIndex = Color_value
Target.Offset(0, -1).Interior.ColorIndex = Color_value
Target.Offset(0, 1).Interior.ColorIndex = Color_value
End If
Bookmarks