By selecting a cell - change color in a range

[hampster]

I am trying to change the color in a range of cells that is triggered
by selecting a separate cell. Example-- select A1 and the range
d5:f10 changes to green. Select another cell - A2 and the previous
selection goes back to its original color and the range associated
with A2 is then changed to green. Etc.... This would be similar to
A1 being a button, but there will be data entered into A1. Any
advice???

 

[Don Guillett]

You can right click the sheet tab>view code>left window select
worksheet>right window use _selectionchange>write your code
Give more examples such as always 3 rows more than the selection and always
cof d:f, etc
and it could be pretty simple to code

 

[hampster]

There is not a set formula such as always 3 rows more than the
selection. Each cell is assigned a range of cells. There are many in
the worksheet. Here are a few actual assignments. A1 - O2:Q4, A2 -
O5:Q6, B2 - R2:T4. In other words, each reference cell refers to a
block of 9 cells. Each cell already contains conditional formatting.
I need some code that recognizes that cell A1 (or other cell) has been
clicked on and then based on which cell is clicked, color a
corresponding range of nine cells and then change back when another is
clicked. A1 (or other cell) may be empty or have data in it.

 

[Don Guillett]

right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then Set x = Range("d5:e6")
If Target.Address = "$A$2" Then Set x = Range("d8:e10")
'etc
Cells.Interior.ColorIndex = xlNone
x.Interior.ColorIndex = 6
End Sub

 

[hampster]

Excellent!!!

Exactly what I was after.
Thank you.

 

[Don Guillett]

Glad it helped. The trick is to not destroy the other formatting.