On click, copy cell contents to another sheet?

[lothario]

Hi,

Sheet1 has cells c3, c4, c5, c6, c7 which contain the colors:

Blue
Red
Green
White
Pink

When the user clicks on a color, I would like to:
a. Copy that color to cell G9 in Sheet3
b. Take the user to Sheet2

How do I do that in VBA code?

Thanks,
Luther

 

[Bernie Deitrick]

Luther,

You can use the worksheet selection change event, code below.
Right-click on sheet1's tab, select view code, and paste the code into
the window that appears.

From your post, it was unclear if you had the colors as fill colors or
the words as values. If you have just words, delete the .ColorIndex
line, if fill colors, delete the .Value line.

HTH,
Bernie

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C7")) Is Nothing Then
Worksheets("Sheet3").Range("G9").Value = Target.Value
Worksheets("Sheet3").Range("G9").Interior.ColorIndex = _
Target.Interior.ColorIndex
Worksheets("Sheet2").Activate
End If
End Sub

 

[lothario]

Thanks Bernie. This did help.

I should have been more specific.
I intend to use the words as values.