This is a cute trick that can be used to impress your boss or co-workers, if they happen to be walking by. Just place any of these code blocks in the sheet module behind a worksheet, then click around the worksheet. Don't use a worksheet that already has cells with fill color, as these procedures will wipe them out.
Highlight active cell row and column
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' color row and column With Target ' only highlight if one cell is selected If .Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' reset worksheet colors Cells.Interior.ColorIndex = xlColorIndexNone ' highlight current column and row Range(.Address, Cells(1, Range(.Address).Column).Address).Interior.ColorIndex = 45 Range(.Address, Cells(Range(.Address).Row, 1).Address).Interior.ColorIndex = 45 Application.ScreenUpdating = True End With End Sub
Highlight active cell row
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' color only row With Target ' only highlight if one cell is selected If .Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' reset worksheet colors Cells.Interior.ColorIndex = xlColorIndexNone ' highlight current row Range(.Address, Cells(Range(.Address).Row, 1).Address).Interior.ColorIndex = 45 Application.ScreenUpdating = True End With End Sub
Highlight active cell column
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' color only column
With Target
' only highlight if one cell is selected
If .Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' reset worksheet colors
Cells.Interior.ColorIndex = xlColorIndexNone
' highlight current column
Range(.Address, Cells(1, Range(.Address).Column).Address).Interior.ColorIndex = 45
Application.ScreenUpdating = True
End With
End Sub
Highlight active cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' color only target cell
With Target
' only highlight if one cell is selected
If .Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' reset worksheet colors
Cells.Interior.ColorIndex = xlColorIndexNone
' highlight current cell only
Target.Interior.ColorIndex = 45
Application.ScreenUpdating = True
End With
End Sub
Highlight active cell, don't reset worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' color cells, don't clear worksheet
With Target
' only highlight if one cell is selected
If .Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' highlight current cell
Target.Interior.ColorIndex = 45
Application.ScreenUpdating = True
End With
End Sub
About JP
8 Comments:
This code errors out for me at
If .Cells.Count > 1 Then Exit Sub "Compile error; expected end of statement.
Unfortunately, I'm too inexperienced to know the correct fix.
Are you highlighting the entire code block and copying/pasting? If you do that then it should work.
Thanks for getting back to me so quickly.
Actually…
What I did was copy/paste each piece of code to sheet 1 of the workbook then later came back and copied/pasted from sheet 1 onto the code module. I've done that many times before without a problem.
I tried again just now, copying directly from here and it works fine.
I've used Excel 2003 for years but was recently "upgraded" to 2007, do you suppose that's what made the difference? (I hate 2007)
Thanks again, Venus
You would only paste one code block at a time. So only one "Private Sub … End Sub" code block should be present in the VB Editor. It should be placed in the code module for the sheet, not a standard module.
I don't think Excel 2007 changed anything in this regard.
Yep, that's what I did.
To be exact, I copied/pasted each block onto an Excel "sheet", then saved the workbook so I could play with it later in the day. (I've done that before in Excel 2003 without problems.) Later, I copied one of blocks of code from the worksheet and pasted it into the code module for the sheet. It erred on the first try, "Compile error, sub or function not defined", with a highlighted space at the beginning of the line. I would delete the space then it would error on the next empty space/line. This happened even on the commented lines.
I just tried it again, same result. Just for jollies, I copied the code into Word, then into the Excel sheet module, it worked fine.
My thinking is that copying from the worksheet left some sort of unseen "something" in that space causing the error; that's just my uneducated guess. I once had a friend tell me that if there was a bug to find, I'd be the one to find it, hah.
I'm curious if you have the same results doing what I did.
Thanks, Venus
The extra cutting and pasting may have introduced some additional characters. That's the only explanation I can think of. I usually just paste directly from the web to the VB IDE.
I would normally paste directly from the web too. I should have just saved the web page and come back to it later when I had more time. Now I know better.
I've set up a calendar for a tracking appt. their is several of the smae people on it.
I want to use a color to represent each person & just put it in a time slot. is there a formula you could give me?