How To and What... code to enter into VisuaBasic Module to highlight active row/column '07

**kelbmccarthy** · 03-20-2014, 03:41 PM

Good Afternoon! I am working on Excel 2007... and I have approximately 2,000+ rows of data. I've been looking around for a way to have the row/column highlighted yellow of the cell I'm currently typing in... and then when I move on to the next highlight those and return the previous cells to the way they were before and highlight the new row/column of the cell selected highlighted yellow. I am not necessarily an excel novice however I've not ever used Visual Basic and am having a hard time getting other codes I've found via my search to run properly. Does anyone have a code I could use? ... and along with that tips/steps on how to get it to run?

SO MANY THANKS IN ADVANCE!!

**Sbarro79** · 03-20-2014, 03:58 PM

Hi

Check, if this code meets your requirements:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

 Dim RngRow          As Range 
 Dim RngCol          As Range 
 Dim RngFinal        As Range 
 Dim Row             As Long 
 Dim Col             As Long 

     Cells.Interior.ColorIndex = xlNone 
     
     Row = Target.Row 
     Col = Target.Column 
     
     Set RngRow = Range("A" & Row, Target) 
     Set RngCol = Range(Cells(1, Col), Target) 
     Set RngFinal = Union(RngRow, RngCol) 
     
     RngFinal.Interior.ColorIndex = 6 

 End Sub

Disclosure: this is not my code, I just found it on an excel forum website, but I don't remember which one and who is the author of this code.

**Draconi** · 03-20-2014, 04:38 PM

The code will run automatically every time you select a new cell. Here is how you can make it work:

1) ALT + F11
2) Double Click "ThisWorkbook"
3) CTRL + S
4) Save as: Excel Macro-Enabled Workbook (.xlsm)

**kelbmccarthy** · 03-21-2014, 08:50 AM

thank you so much! my only remaining question would be ... is there a code that works with cells that are already colored? thus returning them to the original color after I've moved on to the next?

I really appreciate all of the help - thanks so much!

**Draconi** · 03-20-2014, 04:32 PM

Hi,

Here is what you can use:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim newCell As Variant
    Static oldCell As Variant

    newCell = ActiveCell.Address
    Range(newCell).Interior.Color = vbYellow
    If ActiveCell.Address = newCell Then
        On Error Resume Next
        Range(oldCell).Interior.Color = xlNone
        oldCell = newCell
    End If
End Sub

How To and What... code to enter into VisuaBasic Module to highlight active row/column '07

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How To and What... code to enter into VisuaBasic Module to highlight active row/column '07

Re: How To and What... code to enter into VisuaBasic Module to highlight active row/column

Re: How To and What... code to enter into VisuaBasic Module to highlight active row/column

Re: How To and What... code to enter into VisuaBasic Module to highlight active row/column

Re: How To and What... code to enter into VisuaBasic Module to highlight active row/column

Thread Information

Users Browsing this Thread

Similar Threads

How To and What... code to enter into VisuaBasic Module to highlight active row/column '07

Code problem: Can't declare class module userform textbox as variable and enter value ther

[SOLVED] Highlight Active Row and Column.

Using code to highlight active row. Can I toggle on/off on the fly?

How to highlight active row and column?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions