macro to highlight rows based on selected cell

[neodjandre]

Hello,

I was after a macro that would highlight the entire row of a selected cell.

The tricky part is that I would also like to highlight at the same time the 10th row before and after the selected cell.

This would obviously raise some problems if cell A1 is selected because there are no rows before row 1.

could someone please help with this?

edit: if more than one cell is selected, then the macro should do nothing to avoid further complications...

thanks
andy

 

[Peter_SSs]

Well, this could destroy any other cell colour formatting you have, but give it a try. Post back if you need help with implementation.

<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>    <SPAN style="color:#00007F">Dim</SPAN> HighlightRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    ActiveSheet.Cells.Interior.ColorIndex = xlNone<br>    <SPAN style="color:#00007F">If</SPAN> Selection.Rows.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Rows(Target.Row)<br>        <SPAN style="color:#00007F">If</SPAN> Target.Row > 10 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row - 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Row < Rows.Count - 10 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row + 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        HighlightRange.Interior.ColorIndex = 3<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[neodjandre]

Peter,

thanks for the code. Is it possible to modify your code to use conditional formatting instead, so that the formatting of cells is not destroyed ?

thanks a lot for your help
andy

 

[Peter_SSs]

Peter,

thanks for the code. Is it possible to modify your code to use conditional formatting instead, so that the formatting of cells is not destroyed ?

thanks a lot for your help
andy

Does the sheet already have any Conditional Formatting that would also be destroyed if your request is possible? (I am still pondering a way to do that)

 

[neodjandre]

yes it does but only one condition..

so if somehow we managed to use conditional formatting for the second or third conditions, then the conditional formatting would not be affected...

in any case, to make life easier, I don't mind if conditional formatting is destroyed...

 

[Peter_SSs]

in any case, to make life easier, I don't mind if conditional formatting is destroyed...

Based on that, try...

<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>    <SPAN style="color:#00007F">Dim</SPAN> HighlightRange <SPAN style="color:#00007F">As</SPAN> Range<br><br>    ActiveSheet.Cells.FormatConditions.Delete<br>    <SPAN style="color:#00007F">If</SPAN> Selection.Rows.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Rows(Target.Row)<br>        <SPAN style="color:#00007F">If</SPAN> Target.Row > 10 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row - 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Row < Rows.Count - 10 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row + 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> HighlightRange<br>            .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"<br>            .FormatConditions(1).Interior.ColorIndex = 3<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Edit: Note also that this could easily be modified to colour rows based on the Active cell, so that you could get some highlighting even if a selection containing multiple rows is made.

 

[neodjandre]

Peter, that works great... is there an easy way to limit the macro to exclude the 5 first rows in the worksheet so that the conditional formatting for those rows is not deleted?

I tried changing the statement 'ActiveSheet.Cells.FormatConditions.Delete' but I got errors.

If not, then thanks for all your hard work

andy

 

[Peter_SSs]

Try this one:

<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>    <SPAN style="color:#00007F">Dim</SPAN> HighlightRange <SPAN style="color:#00007F">As</SPAN> Range<br><br>    ActiveSheet.Cells.Resize(Rows.Count - 5).Offset(5).FormatConditions.Delete<br>    <SPAN style="color:#00007F">If</SPAN> Selection.Rows.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Rows(Target.Row)<br>        <SPAN style="color:#00007F">If</SPAN> Target.Row > 15 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row - 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Row < Rows.Count - 10 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> HighlightRange = Union(HighlightRange, Rows(Target.Row + 10))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> HighlightRange<br>            .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"<br>            .FormatConditions(1).Interior.ColorIndex = 3<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[neodjandre]

brilliant.. thanks Peter