Highlight row when click a cell in that row

[mr names]

Hi Guys

Basically I am running a gantt chart (critical path) in excel with dates across the top and tasks running down the left.

When i click in a cell on the critical path, I would like the row and column to highlight or change colour in some way so that it is easiier to see what task I am on?

Hope this makes sense

 

[VoG]

Hi.

Select the area that you want this to apply to then use Conditional Formatting with the following formula

=ROW()=CELL("row")

and set a fill color.

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

then press ALT + Q to close the code window.

 

[Smitty]

Welcome to the Board!

This will do it, just be warned that it will wipe out any existing formatting, so like with all code examples, try it on a test workbook first:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)<br>    Cells.Interior.ColorIndex = xlNone<br>    <SPAN style="color:#00007F">With</SPAN> ActiveCell<br>        .EntireRow.Interior.ColorIndex = 36<br>        .EntireColumn.Interior.ColorIndex = 36<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

EDIT: nevermind, Peter's is a much cleaner approach.

 

[mr names]

That works perfect! Genious!

One last thing however...Is there a simple way of making it so that the column also changes colour? To give it an almost 'cross hairs' style?

 

[mr names]

Welcome to the Board!

This will do it, just be warned that it will wipe out any existing formatting, so like with all code examples, try it on a test workbook first:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = xlNone
With ActiveCell
.EntireRow.Interior.ColorIndex = 36
.EntireColumn.Interior.ColorIndex = 36
End With
End Sub


THanks Smitty! I'm working on a newly created version with no formatting so it's not replacing anything at present...Thanks for the reply though, much appreciated!

 

[VoG]

Change the CF formula to

=OR(ROW()=CELL("row"),COLUMN()=CELL("col"))

 

[mr names]

Again, works perfectly!

Thanks very much for taking the time to respond!!