Click on an empty cell and the contents of another cell will appear

[Michael Lanier]

Is it possible to click on an empty cell and by doing so, the contents
(in this case a symbol) in another cell will appear? I want this to
apply in a range of cells such as B10:B20. The symbol to appear is in
C1. Thanks for any help.

Michael

 

[Bernie Deitrick]

Michael,

Right-click the sheet tab, select 'View Code" and paste this code in the window that appears. See
in-line comments to choose which way to go....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B10:B20")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "" Then Exit Sub
Application.EnableEvents = False
'If you only need the symbol (no formatting needed), use
Target.Value = Range("C1").Value
'If you need formatting (font) to show the symbol, then use
Range("C1").Copy Target
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

 

[Michael Lanier]

Thanks Bernie. Your macro works like a charm.

Michael