colour a row by code

  • Thread starter Thread starter Jock
  • Start date Start date
J

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,
 
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: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
 
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
 
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?
 
add this line:
Cancel = True

Jock said:
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?
Click to expand...
 
Back
Top