PDA

View Full Version : by click on cell of column a the color of row change



parscon
07-14-2013, 10:57 AM
I need VBA code that when I click on column a on each cell a1 to a... the color of that row will be yellow .
and if click back the color will not change .
example :
when click on column a cell a1 the color of row that row will be yellow.
thank you

p45cal
07-14-2013, 11:34 AM
To do this with a single left-click would be possible with a selection_change event but it wouldn't be just a click that would cause a colour change; moving around the sheet with the arrow keys would also trigger a colour change.
If your happy to accept ewither a right-click or a double-click them one of these macros in the sheet concerned's code-module might do it for you:Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Cancel = True
Target.EntireRow.Interior.ColorIndex = 6
End If
End Sub
or
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Cells.Count = 1 Then
Cancel = True
Target.EntireRow.Interior.ColorIndex = 6
End If
End Sub

Teeroy
07-14-2013, 03:28 PM
Another possibility is you could also use a modeless userform with a button to change the color of the row of the Activecell.

SamT
07-15-2013, 07:38 AM
In both of p45cal's subs, change this lineTarget.EntireRow.Interior.ColorIndex = 6
To this codeWith Target.EntireRow.Interior
If .ColorIndex = 6 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 6
End If
End With
Since you have to double click a cell to edit the contents, (as opposed to replacing them,) and since you have to right click a cell to do many things to the cell, you may want more control of when to color the rows.

This sub requires that you first double click one cell in Column "A" before right clicking the same cell.Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Must first double click one cell in column "A" for this to run

If Target <> ActiveCell Then Exit Sub
Cancel = True

With ActiveCell.EntireRow.Interior
If .ColorIndex = 6 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 6
End If
End With

End Sub

You can combine the two Mouse events by first double clicking a cell to Activate it then right clicking it to trigger the color change sub only on the Active Cell.