#1
|
|||
|
|||
highlight the entire row of current cell
Hi. Can someone please tell me if it is possible to highlight the entire row containing the current cell in excel 2010. I have searched on the net but can only find references to using VBA, of which I have no knowledge. Thank you |
#2
|
||||
|
||||
If you want to do it manually, I suppose the easiest way is the following key sequence:
<Shift-Space> (which selects the entire row for the current cell. If the current selection takes in cells on more than one row, the selection is extended to all those rows.) <Alt-H> (which selects the Home tab on the ribbon, if you're using Excel 2010. If you're using an earlier version, this sequence will have to change.) <H> (to open the highlighting window) Then click on the desired color. There may be a keystroke that will let you select the color, but I'm not aware of it. And there might even be a way to create a keystroke that will do all of the above, without writing something in VBA. But I tend to use VBA a lot, so if there's another way I'm not sure what it might be. |
#3
|
|||
|
|||
Hi Bob, thank you for this information. I currently use the "shift+space", but I wondered if it was possible to get the row to highlight automatically when I paged down in a spreadsheet.
|
#4
|
||||
|
||||
It's not very complicated to add the VBA solution
Open a workbook Hit Alt+F11 ( VB editor opens) Clcik View - Project explorer ( it's usually open by default) Select the sheet where you want to implement the macro A window opens Paste this code Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' Clear the color of all the cells Target.Parent.Cells.Interior.ColorIndex = 0 With Target ' Highlight the entire row and column that contain the active cell .EntireRow.Interior.ColorIndex = 8 End With Application.ScreenUpdating = True End Sub From now on the row will highlight when a cell is selected Play around with the macro a bit to change the color there where it says .EntireRow.Interior.ColorIndex = 8 Change the 8 to another value It works for me and I know nothing about VB
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Thank you so much. That works perfectly, although I didn't think it was going to as I didn't get the prompt to save. One last question please... how do I cancel a solution?
|
#6
|
||||
|
||||
What do you mean by "cancelling a solution"?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
||||
|
||||
You mean, how do you turn off this effect? If you want to do it permanently you can just delete the subroutine. But if you want to turn it off temporarily, I can think of two easy ways:
1) Change the name of the subroutine slightly. It's named "Worksheet_SelectionChange", and it must be to work automatically; so if you make the name "Worksheet_SelectionChange_Off" (for example), it will cease to work until you remove the extra characters. 2) The way I'd probably do it is add a line or two to the program, like this: Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const Disabled = False If Disabled Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub Target.Parent.Cells.Interior.ColorIndex = 0 Target.EntireRow.Interior.ColorIndex = 8 End Sub |
#8
|
|||
|
|||
What if I get a code that says "project unviewable"?
|
#9
|
||||
|
||||
Kob, I think you meant to attach this question to another thread. This one is about something else.
If you're on-topic for this thread, we need more detail. And some folks will insist that you start a new thread, instead of attaching your question to an old one; I'm not sure why, but it's a common complaint and it might be simpler just to do it. Either way, we would need to know some background. |
#10
|
|||
|
|||
How can I highlight the entire row containing the current cell in excel 2010
Hi,
Yes, it looks like my question is attached to the correct thread. When I hit Alt F11 and selected the spread sheet that I wanted to add the code to, I get a window that says " Project Locked" "Project unviewable". The sheet doesn't appear to be locked. I checked. I'm just trying to make the active rows highlighted to make viewing this document easier. It's large. |
#11
|
||||
|
||||
<Alt-F11>? Hey, that's cool; I didn't know about that shortcut. (I always use <Alt-L>,V.) Well, I don't know what to tell you about locked projects. If you're willing to upload the workbook, I can look at it myself; and if it does the same thing for me, I'll play around with it, see whether I can figure out why.
If your workbook contains sensitive material and you should not upload it, maybe this will help: I just googled "excel project locked" and it came up with a number of pertinent-looking discussions, including this one which is out of date but clearly on-topic; maybe one of those will clear things up for you. |
#12
|
|||
|
|||
I am already highlighting cells in a table.
My row is highlighted depending on a bunch of specific words, written in a specific column. so the code provided does highlight the row, but underneath the already highlighted row. |
#13
|
|||
|
|||
Quick question regarding the row highlighting.....how can this be automated to highlight the current date (the entire row to be highlighted),,conditional formatting can highlight the date only...but how about the entire row?
|
#14
|
||||
|
||||
Suppose the date is in A1, select row1, and use =$A1=today() as condition for the CF then format as required ( the $ sign is the trick)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#15
|
||||
|
||||
Aww, Peco beat me to it. If you want to do it with VBA it's easy, but he's right, you should be able to do it with conditional formatting too.
If you still want to write a macro to do it, or if you already have a VBA program and you want to teach it to do something to the entire row, you just need the EntireRow property of the date cell. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2007, highlight entire row based on data found in one cell | MSofficeBLUE | Excel | 2 | 10-15-2013 09:51 PM |
Is there a way to automatically highlight the column and the row that of the current | Jamal NUMAN | Excel | 8 | 02-14-2012 02:58 PM |
How to highlight the row based on cell value? | Learner7 | Excel | 1 | 12-11-2011 02:28 AM |
Macro for moving one cell down from current position | Johnny thunder | Word VBA | 3 | 04-07-2011 04:44 PM |
highlight cell after checkbox | flatk | Word | 0 | 01-25-2007 12:32 PM |