Highlight Row

zotee98 · Jul 21st 2006

I Would Like Some Help Writing A Macro That Will Highlight The Entire Row Where An Active Cell Is Selected. The Only Kicker Is That You Can Not Use Conditional Formating. I Have Many Cells That Use Conditional Formatting Where I Have Used All 3 Conditions. The Other Option Is That If Someone Knows A Way To Get A Cell To Change To A Different Color Based On Whats In The Cell That Would Work Great Too. I Need More Then Just 3 Conditional Formats. So I Guess This Is A Two Part Question.

Thanks In Advance

Bill Rockenbach · Jul 21st 2006

Re: Highlight Row

HI zotee98,

One way is to use a Worksheet_Change procedure and a Select Case. In the following code any cell in the active spreadsheet will change color if a number 1 through 6 is entered into the cell. Any other number will change the color to “no fell”.

Code

Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Target.Value


Case 1: Target.Interior.ColorIndex = 3
Case 2: Target.Interior.ColorIndex = 1
Case 3: Target.Interior.ColorIndex = 16
Case 4: Target.Interior.ColorIndex = 30
Case 5: Target.Interior.ColorIndex = 22
Case 6: Target.Interior.ColorIndex = 10


Case Else: Target.Interior.ColorIndex = 0


End Select


End Sub

Display More

Bill

macromike · Jul 21st 2006

Re: Highlight Row

To answer your first question of needing to highlight the entire row of the selected cell, try this below. There is one catch, the highlighting remains on whatever rows you previously selected as well. I'm not sure if you desire this or not

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'This sub goes within the Sheet
'The following will highlight the selected row


If Union(ActiveCell, Range("A1:IV65536")).Address = _
    Range("A1:IV65536").Address Then
    
    ActiveCell.EntireRow.Select
    With Selection.Interior
        .ColorIndex = 6  'yellow color
        .Pattern = xlSolid
    End With


End If
'NOTE: rows remain highlighted even after user clicks on another row
'this may or may not be desired depending on the intent of your program


End Sub

Display More

macromike · Jul 21st 2006

Re: Highlight Row

An excellent solution posted by DROM under the Thread "Highlight a row when a cell in that row is clicked" started by ctg. This thread should appear under similar threads at the top of your screen.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'this highlights the selected cell's entire row
Cells.Interior.ColorIndex = xlNone
With ActiveCell
.EntireRow.Interior.ColorIndex = 27
End With
End Sub

He also posts a solution for having something happen on a particular row selected, like row#5, if desired.

Dave Hawley · Jul 21st 2006

Re: Highlight Row

Be VERY careful with these types of macros as they WILL wipe out any existing backgrounds.

Highlight Row

Participate now!

Share