colour a row by code

[Jock]

If I right click any cell in column "D", how can I get the cells A:M on the
same row to be coloured grey. If right clicked again, A:M to be restored to
original shade (blue)?

Thanks,

 

[Jacob Skaria]

Try the below..Right click the Sheet tab>ViewCode and paste the below code to
the code module...


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D")) Is Nothing Then
Range("A" & Target.Row & ":M" & Target.Row).Interior.ColorIndex = _
IIf(Target.Interior.ColorIndex = 15, 5, 15)
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Patrick Molloy]

the issue is saving the cell move 'from' rather than the new cell.

use the rigth click event. open the code page for the sheet, right-click on
the tab and select View Code, then paste this
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Static oldRow As Long
Static oldColor As Long
If oldRow <> 0 Then
Rows(oldRow).Interior.Color = oldColor
End If
oldColor = Target.Interior.Color
oldRow = Target.Row
Rows(Target.Row).Interior.Color = 12632256
Cancel = True
End Sub



An alternative would be to set conditional formatting

Option Explicit
Sub CF_Example()
With Range("B7:k17")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=CELL(""Row"")=ROW()"
.FormatConditions(1).Interior.ColorIndex = 15
.Select
End With
End Sub

if you select anycell in the table, then F9, that row within the table will
be grey

 

[Jock]

Hi Jacob,
I have used your code and, although it works ok, the right click menu
appears every time (cut, copy, past and so on) it is used.
How can I stop this appearing?
I already had a BeforeDoubleClick event into which I added your code.
Should it be in another on its own?

 

[Patrick Molloy]

add this line:
Cancel = True

Hi Jacob,
I have used your code and, although it works ok, the right click menu
appears every time (cut, copy, past and so on) it is used.
How can I stop this appearing?
I already had a BeforeDoubleClick event into which I added your code.
Should it be in another on its own?