Line Colour

Sharid

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,066
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to set a macro that will highlight the entire ROW in a colour when I select a CELL, so that it is easy for the user to identify the information.

I have a very large worksheet and I am currently using freeze frame, but it still becomes a bit confusing as there is a lot of information on the sheet to look at.

e.g. if I was in column J cell 15 then the whole ROW would change colour to blue

If this is possible, then I would like it to start from Cell 9 and end for Cell 33


Cheers
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
CAUTION: The following will overwrite any manually filled colours and won't override Conditional Formats.

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

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row < 9 Then
    Rows(9).Interior.ColorIndex = xlNone
    Exit Sub
ElseIf Target.Row > 33 Then
    Rows(33).Interior.ColorIndex = xlNone
    Exit Sub
End If
Rows("9:33").Interior.ColorIndex = xlNone
Target.EntireRow.Interior.ColorIndex = 5
End Sub

then close the code window using the X.
 
Upvote 0
This is what I am looking for apart from the bit that overwrite the manually filled colours, is there a work arround to this.

cheers
 
Upvote 0
JUBA said:
This is what I am looking for apart from the bit that overwrite the manually filled colours, is there a work arround to this.

cheers
Click to expand...

Courtesy of Erik in another thread http://www.mrexcel.com/forum/showthread.php?t=328712

Right click the sheet tab and replace the existing code with

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

then close the code window.

Select rows 9 to 33, Format > Conditional Formatting, select Formula Is and enter the formula

=ROW()=CELL("row")

then click the Format button, on the Fill tab select a blue colour then OK.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
565
AndyB86
A
M
  • Question Question
Compare cell colour to other cell colour and trigger action automatically
Replies
7
Views
430
Maxime_Excel
M
O
  • Solved
mod(row() change font colour as required
Replies
7
Views
474
Peter_SSs
Peter_SSs
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
B
  • Question Question
Plug-in/Solution to Clear Up Cumbersome File
Replies
1
Views
170
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,920
Messages
6,175,376
Members
452,638
Latest member
Oluwabukunmi

Share this page

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