How to colour an entire row, on row number click

[MiB1986]

Heya

New to this site, and am new to VBA i read a dummies book so i understand how VBA works but for the life of me cant work out how to do what i want to do.

I simply want to change the background colour of a row when you click the number on the left, like how when you click it draws a box around the row, simply just "highlight" it in yellow. and if you click on another number transfer the highlight to that row and return the row back to its original state.

So to abrevate.
1. Click row number changes entire row yellow
2. If Click another row number change previous row to original colour before yellow and then change entire new row number to yellow.
3. Go back to 1.

I understand it in flow charts etc... just cant work out how to translate it into VBA code.

I think i want to simply toggle the colour of the row which is "active"

Many Thanks for your replies in advance

[lucas]

Hi MiB, what do you mean by click on the row number? do you have a column of numbers or do you just want to highlight the row if you click anywhere in the row?

[p45cal]

There is an example of something like that here.

However put this in the code module for the sheet concerned:
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastHighlitRow As Range
If Not LastHighlitRow Is Nothing Then LastHighlitRow.Interior.ColorIndex = xlNone
Set LastHighlitRow = ActiveCell.EntireRow
LastHighlitRow.Interior.ColorIndex = 6
End Sub[/vba]
It will clear any pre-existing cell background colours apart from conditional formatting of any row you click on/ or select.

If you want it to highlight a row and still want to move about the sheet without moving. then we can do this with a doubleclick, much in the same way.

[MiB1986]

Hi MiB, what do you mean by click on the row number? do you have a column of numbers or do you just want to highlight the row if you click anywhere in the row?

as in the actual row number on the left, the number you right click to hide or un hide a row. i want to click one of those numbers and the above happens.

thanks for your response,

[p45cal]

try this then: (edit this has been adjusted to cope with deleting rows since it ws first posted)
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastHighlitRow As Range
If Target.Address = Target.EntireRow.Address Then
On Error Resume Next
If Not LastHighlitRow Is Nothing Then LastHighlitRow.Interior.ColorIndex = xlNone
On Error GoTo 0
Set LastHighlitRow = ActiveCell.EntireRow
LastHighlitRow.Interior.ColorIndex = 6
End If
End Sub[/vba]

[lucas]

re post #4. That's not going to happen I don't think. You are selecting the row when you do that.

what's wrong with just clicking any cell on the row?

[lucas]

p45cal proved me wrong. Nice one.

[MiB1986]

try this then: (edit this has been adjusted to cope with deleting rows since it ws first posted)
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastHighlitRow As Range
If Target.Address = Target.EntireRow.Address Then
On Error Resume Next
If Not LastHighlitRow Is Nothing Then LastHighlitRow.Interior.ColorIndex = xlNone
On Error GoTo 0
Set LastHighlitRow = ActiveCell.EntireRow
LastHighlitRow.Interior.ColorIndex = 6
End If
End Sub[/vba]

WOW! I am so impressed, wish i could process things that fast, welldone, i thought it wasnt possible either welldone to you, just one thing how can i make it so if i double click the row number 4 and then double click the row number 7 i can compare the highlight rows, so to summarize:

1. Double click on a row to highlight the row,
2. Double click again to remove the highlight

again back to toggles... also is it possible to copy it into a workbook code view rather than each individual sheet.

thanks once again...

infact how could i make it a single click as well, ideally two versions, do i just change it from Click() to DoubleClick() ??

[p45cal]

I can't do the double-click on a row number to toggle the highlighting, but I can do it on a cell double-click. In the code below there are two commented out lines which if un-commented restrict the the double-click action to working on column A (=column 1). Adjust to suit. Otherwise it works when you double-click any cell.
This code needs to be in the ThisWorkbook code module, so that it works on the whole workbook. (Don't forget to remove the older code from the sheet(s) code module(s):
[vba]Option Explicit

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'If Target.Column = 1 Then
Cancel = True
Target.EntireRow.Interior.ColorIndex = IIf(Target.EntireRow.Interior.ColorIndex = 6, -4142, 6)
'End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Static LastHighlitRow As Range
If Target.Address = Target.EntireRow.Address Then
On Error Resume Next
If Not LastHighlitRow Is Nothing And Sh Is LastHighlitRow.Parent Then LastHighlitRow.Interior.ColorIndex = xlNone
On Error GoTo 0
Set LastHighlitRow = ActiveCell.EntireRow
LastHighlitRow.Interior.ColorIndex = 6
End If
End Sub[/vba]

[Eds]

Great post! Learned a lot! Thanks for the question and thanks to p45cal!

Eduardo