copy cell interior color from clicked cell to active cell

Question

Hi,

I have a spreadsheet that has a colour key at the top. Users are expected to manually set the colour of various cells in the spreadsheet in line with the colour key.

Ideally what I'd like is for users to be able to highlight/select one or more cells in the spreadsheet they wish to give a colour to, then with one click on the colour key cell be able to copy its interior colour to the cells they selected originally.

Example: User selects cells A17:D17 and wants to set the interior colour of those cells the same as the interior colour of cell A2 just by clicking on cell A2.

They might then want to change the colour of A17:D17 to the same colour as A3 just by clicking on cell A3

Is there a macro/custom function/combobox way of doing this?

Any advice would be greatly appreciated

Best Regards, Ben

Answer 1

Adding a command button for each color to do this. Have the users select cells, and then click the button to change the .ColorIndex property of the Selection to the button's .ColorIndex.

Answer 2

Actually there is a builtin way of doing this but i don't know whether your users will like it or not... if they don't then add a custom command button to copy the colorIndex property.

Manual process:

1. Copy the cell containing the color key.

2. Click on the new cell you wish to add a color to, and right click select paste special..

3. Select Format option from the window that appears and click ok.

The color, formatting etc is copied without the value.

Answer 3

@A. Scagnelli's idea is a much better implementation of what you need. Just have a bunch of colour buttons changing the Color index.

The reason why is that tracking the previous selection is a pain. When you make a new selection i don't think Excel knows what your last selection was. You can get around this by setting a global variable to remember each last selection. This isn't nice or recommended .. but it's possible.

You'll need something like the following in your Worksheet;

Option Explicit
Dim lastAddress As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If lastAddress <> "" Then
   Range(lastAddress).Formula = "Was selected before"
   Range(lastAddress).Interior.ColorIndex = 6
 End If

 lastAddress = Target.Address

End Sub

NB: You'll need to test if the current selection is one of your colour cells before firing .Interior.ColorIndex but i'll leave that to you :)