Excel - Selecting A Cell Formats Another Cell

[bibblebub]

I have a worksheet in which I am using some cells in column D as titles for their rows, and what I want to happen when someone clicks on e.g. cell G3 (or H3, I3 etc) to select it is that the fill colour of cell D3 gets changed, just to make it obvious which row is being used. Similarly if cell G6 (or H6…) is selected then the fill colour of D6 changes; and so on. Of course, once a cell in another row is selected then the cell in column D reverts to its original format.

Is there a formula to allow this to be done using conditional formatting or does it require a worksheet/cell event?

TIA

[bazwillrun]

http://i46.tinypic.com/34hctvp.jpg

Theres a few ways and choices of how to do this but they all need a VBA script

this one will highlight the current row to the left of the current selection , see pic in link.
To change the highlight colour you need to change the ColourIndex value, the colours and their numbers can be found with a google search

In Excel make sure you can see the Developer Tab then click on VBA and make sure your screen is showing the same as my screenshot (attached), your VBA window may be slightly differnt because it will show your worksheet names

click on Sheet 1 or whatever your sheet is called and put the script at the bottom of this post in.
Make sure its exactly as in my screenshot, and save the workbook as macro enabled .xlsm or whatever version of Excel you use equivalent.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target
If .Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlColorIndexNone
Range(.Address, Cells(Range(.Address).Row, 1).Address).Interior.ColorIndex = 24
Application.ScreenUpdating = True
End With

End Sub

[bibblebub]

Thank you. I'll give that a try.

[bibblebub]

I've just tried it - I should have mentioned in the OP that I'm using Excel 2010 - and can restrict the colouring to the columns that I want by changing the parameter in the range() function to get the result that I want.

So, thanks again.