IF cell is green add date

I am looking to have a cell M4 populate when I change either J4, K4 or L4 to a green fill.

For example: If I put the date 12-14 in K4 and change the background fill to Green, I want M4 to populate with the date 12-14.

1 Answer

To solve your problem, I would like to suggest a few macros.

Macro 1:

Private Sub Worksheet_Change(ByVal Target As Range) Set w = ActiveSheet.Range("J4:L4") For Each C In w If C.Value <> "" And Not IsDate(C) Then C.ClearContents MsgBox "Only a Date is permitted in this cell." End If If C.Value = "" And Not IsDate(C) Then C.Interior.ColorIndex = 0 Else C.Interior.ColorIndex = 4 End If Next C
End Sub

Macro 2:

Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub

How the Macro works:

• At the Active Sheet press ALT+F11 to open VBA Editor.
• Copy & Paste both Macro as Standard Module.
• The 1st Macro will only allow to enter DATE in Cells J4:L4 and highlight cell/cells in Green Color, if finds DATE value.
• RUN the Macro 2 when you want to Copy the Last Cell's value from J4:L4 to Cell M4.

N.B.

• I've used the LAST Cell Method to Copy to M4 because it's very much possible that you may enter DATE in all cells or in few.
• Data Range J4:L4, destination Cell M4 & Color Index values are editable.