Highlight Cell MXXX

[sachavez]

Excel 2007

I'm looking for some VBA that will highlight the cell in Column M of the row that I am working in.

Example, if I am working in row 35, cell M35 would be highlighted in pink. If I jump to row 1005, cell M1005 would be highlighted in pink.

Is this possible?

Thanks, in advance

 

[VoG]

Select column M, go to Conditional Formatting, Formula Is, use the formula

=ROW()=CELL("row")

and apply a fill colour.

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

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

 

[sachavez]

Pretty slick, VoG! Problem solved.

Thank you very much!!!

Steve

 

[Peter_SSs]

Another option (with slightly different results) that may be of interest is to not have Conditional Formatting in the column but have this SelectionChange code

<font face=Courier New><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> Range)<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns("M").Interior.ColorIndex = xlNone<br>    Intersect(Target.EntireRow, Columns("M")).Interior.ColorIndex = 38<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


The difference is if you select a range containing multiple rows, or even a disjoint range like H5:H8 and J12:K15. Not sure if that's better or worse for your circumstance.

 

[sachavez]

Peter,

Both options work like a champ. I'm definately going to use one or both of these options going forward.

Thanks again!

Steve