Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour a row by code
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, -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour a row by code
Try the below..Right click the Sheet tabViewCode 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 --------------- Jacob Skaria "Jock" wrote: 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, -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour a row by code
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" wrote: 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, -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour a row by code
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? -- Traa Dy Liooar Jock "Jacob Skaria" wrote: Try the below..Right click the Sheet tabViewCode 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 --------------- Jacob Skaria "Jock" wrote: 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, -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour a row by code
add this line:
Cancel = True "Jock" wrote in message ... 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? -- Traa Dy Liooar Jock "Jacob Skaria" wrote: Try the below..Right click the Sheet tabViewCode 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 --------------- Jacob Skaria "Jock" wrote: 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, -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colour format code | Excel Programming | |||
Can I rate a cell 1-5 and colour code it? | Excel Discussion (Misc queries) | |||
How can I rate a cell 1-5 and colour code it? | Excel Worksheet Functions | |||
Code to change interior colour only if current interior colour is | Excel Programming | |||
Code to colour Unlocked cells | Excel Programming |