Copy cell contents and formatting

[excellence]

The enclosed macro puts the contents of N15 to the first empty cell in column D. The contents of column D are conditionally formatted for duplicates. I would like that whatever is added to the last cell in column D to be pasted WITH its formatting to cell E22 at the end of this macro. This would help me see those contents without having to scroll down lots of rows to see if it is a duplicate. I can't figure what to add to this macro to make it happen.
Many thanks

Sub macrooo()
Range("N15").Select
    Selection.Copy
    
With ActiveSheet
Set currentcell = .Cells(.Rows.Count, "d").End(xlUp)
If IsEmpty(currentcell) Then
'do nothing
Else

currentcell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


End If

End With


End Sub

 

[Joe4]

I am a little confused over the relationship between columns D and E. You mention Conditional Formatting in column D, but then are talking about formatting in column E.

Can you show us an example of your data and expected output? And can you show us your Conditional Formatting rules?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[excellence]

I am a little confused over the relationship between columns D and E. You mention Conditional Formatting in column D, but then are talking about formatting in column E.

Can you show us an example of your data and expected output? And can you show us your Conditional Formatting rules?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sorry, but I have had significant problems with XL2BB so if you can help with these pics and further explanation it would be appreciated otherwise I will have to forego the issue.

A code is entered in N15
When the enter code button is pressed it puts N15 into the last non empty cell in column D
IF the code is a duplicate, it will format the code as pink (conditionally formatted).
I "simply" want to take whatever is entered into the last cell in column D to be copied as is to cell E22
This would allow me to see the last cell entry after running the macro in E22 without having to scroll down sometimes hundreds of rows to see its contents
Enclosed pics of spreadsheet and conditional formatting…note the pink duplicate in D, which I would want copied to E22, realizing that the D entry will change after every entry and hence E22 would change appropriately.

Thanks

 

[excellence]

I am a little confused over the relationship between columns D and E. You mention Conditional Formatting in column D, but then are talking about formatting in column E.

Can you show us an example of your data and expected output? And can you show us your Conditional Formatting rules?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sorry, but I have had significant problems with XL2BB so if you can help with these pics and further explanation it would be appreciated otherwise I will have to forego the issue.

A code is entered in N15
When the enter code button is pressed it puts N15 into the last non empty cell in column D
IF the code is a duplicate, it will format the code as pink (conditionally formatted).
I "simply" want to take whatever is entered into the last cell in column D to be copied as is to cell E22
This would allow me to see the last cell entry after running the macro in E22 without having to scroll down sometimes hundreds of rows to see its contents
Enclosed pics of spreadsheet and conditional formatting…note the pink duplicate in D, which I would want copied to E22, realizing that the D entry will change after every entry and hence E22 would change appropriately.

Thanks
View attachment 112702View attachment 112703View attachment 112704

alternatively, is there an Excel function that I could place in E22 that would copy the last non empty cell in D and paste it with its formatting to E22? I've tried this but need the formatting to go with it
=LOOKUP(2,1/(D15:D3000<>""),D15:D3000)