macro to highlight rows based on selected cell

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
950
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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>
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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...
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top