Highlighting 'corresponding' cells

[R. Todd Miller]

I have a table like the one below. Is it possible to highlight an
offset cell depending upon which cell is currently selected? In other
words, if I click on cell A1, can the background color of cell D1
change to say yellow. Conversely, if I click on cell D1 I would want
the background color of cell A1 to change. Any ideas? Thanks in
advance.

A B C D E F
1 a b c 1 2 3
2 d e f 4 5 6
3 g h i 7 8 9

 

[Patrick Molloy]

You can use conditional formatting for this
example: Highlight a cell in column A of a table in A:E
select all the cells in column A then Format/Conditional
Formatting , change to ''Formula Is' and enter
=AND(CELL("ROW")=ROW(),CELL("COL")<=5)
seelct Patterns and choose a pale background color...OK
twice to close

Now go to the sheet's code page ( right click the tab to
get the menu & choose View code
add this
Private Sub Worksheet_SelectionChange(ByVal Target As _
Range)
Sheet1.Calculate
End Sub

Now, for any cell in the table, the corresponding cell in
A will be highlighted.

Now seelct column D in the table and add this 'formula is'
=AND(CELL("COL")=1,ROW()=CELL("ROW"))
choose another pattern

Now if a cell in the table first colun is selected, then
the cell in D for that row will also be hightlighted.


Patrick Molloy
Microsoft Excel MVP

 

[R. Todd Miller]

Thanks Patrick. That's beautiful!