User copy&paste without BG color

[treva26]

Hi,

I have a workbook that gets data from a database and puts it in tables for the user.

They need to be able to copy from that table and paste into a similar table below.

However the first table has a gray background, and I don't want it to paste that color into the second table.

Obviously they could paste as values but these are not advanced users, I need it simple for them.

I have attached an example workbook.

Thanks.

[teylyn]

Educate your users! They may actually enjoy being treated as people with a capacity to learn.

Instead of creating a macro and telling them to copy and then use the button to paste, ...

tell them to copy the data, click the target cell, then use the magic special command:

hold down the Alt key, press E, release the Alt key, press S, press V, press Enter. This can be really quick: Alt - E - S - V - Enter.

That can't be too hard for even the most basic user to "get"? I work with them every day and I've seen my share of special cases, but I'm quite hesitant to write a macro to duplicate native Excel functionality when the gain is just one or two key presses.

cheers

[treva26]

Well unfortunately they wont like that at all.
Plus the easier I make it for them the better I look!

I went with a little VBA on the OnChange event of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'reset table background colours

Application.ScreenUpdating = False       'dont update the screen 

'check what row the change was made at...   and set that table to white background
If Target.Row > 31 And Target.Row < 39 Then Range("B33:R37").Interior.ColorIndex = 2
If Target.Row > 50 And Target.Row < 58 Then Range("B52:R56").Interior.ColorIndex = 2
If Target.Row > 79 And Target.Row < 77 Then Range("B71:R75").Interior.ColorIndex = 2
If Target.Row > 88 And Target.Row < 96 Then Range("B90:R94").Interior.ColorIndex = 2

Application.ScreenUpdating = True        'screen updating back on

End Sub

[treva26]

Although maybe this is simpler and just as fast...

Private Sub Worksheet_Change(ByVal Target As Range)
 
 'reset table background colours
 Range("B33:R37,B52:R56,B71:R75,B90:R94").Interior.ColorIndex = 2

End Sub