Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color when another cell is selected
Hi all,
I've been searching this group, couldn't find answers to my question below: Row 1 is name of the places Column A is dates of the year. When a cell is selected in the area in the middle, I would like the place name and date in Row1 and ColumnA to be highlighted as well. I know there is a RowLiner that you can download and use, but it's not really working. I guess it's because Row1 and ColumnA are splited and frozen pane. Anything that we could do in conditional formatting or even VBA? Thanks in advance Toto Sanderson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color when another cell is selected
Try:
Right click on tab==View code and copy/paste the code Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo wsexit: Application.EnableEvents = False Rows(1).Interior.ColorIndex = 0 Columns(1).Interior.ColorIndex = 0 If Target.Row = 1 Or Target.Column = 1 Then GoTo wsexit Cells(Target.Row, 1).Interior.ColorIndex = 3 Cells(1, Target.Column).Interior.ColorIndex = 3 wsexit: Application.EnableEvents = True End Sub HTH "Toto Sanderson" wrote: Hi all, I've been searching this group, couldn't find answers to my question below: Row 1 is name of the places Column A is dates of the year. When a cell is selected in the area in the middle, I would like the place name and date in Row1 and ColumnA to be highlighted as well. I know there is a RowLiner that you can download and use, but it's not really working. I guess it's because Row1 and ColumnA are splited and frozen pane. Anything that we could do in conditional formatting or even VBA? Thanks in advance Toto Sanderson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color when another cell is selected
Does this work
'---------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target With .EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With .Interior.ColorIndex = 20 End With End With With .EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With .Interior.ColorIndex = 20 End With End With .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 36 End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Toto Sanderson" wrote in message ... Hi all, I've been searching this group, couldn't find answers to my question below: Row 1 is name of the places Column A is dates of the year. When a cell is selected in the area in the middle, I would like the place name and date in Row1 and ColumnA to be highlighted as well. I know there is a RowLiner that you can download and use, but it's not really working. I guess it's because Row1 and ColumnA are splited and frozen pane. Anything that we could do in conditional formatting or even VBA? Thanks in advance Toto Sanderson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color when another cell is selected
Paste following code into Worksheets's code
(right-click on sheet tab, select View Code) HTH -- AP '-------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const sWatchRange = "B2:E30" ' <== Change Static rOldCell As Range Dim rRowHeader As Range Dim rColHeader As Range If Not rOldCell Is Nothing Then setHeaderColor rOldCell, xlColorIndexNone End If If Intersect(ActiveCell, Range(sWatchRange)) Is Nothing Then Exit Sub setHeaderColor ActiveCell, 6 Set rOldCell = ActiveCell End Sub Sub setHeaderColor(rCell As Range, iColor As Integer) Dim rRowHeader As Range Dim rColHeader As Range Set rRowHeader = Cells(rCell.Row, 1) Set rColHeader = Cells(1, rCell.Column) With Union(rRowHeader, rColHeader) .Interior.ColorIndex = iColor End With End Sub '---------------------------------------- "Toto Sanderson" a écrit dans le message de news: ... Hi all, I've been searching this group, couldn't find answers to my question below: Row 1 is name of the places Column A is dates of the year. When a cell is selected in the area in the middle, I would like the place name and date in Row1 and ColumnA to be highlighted as well. I know there is a RowLiner that you can download and use, but it's not really working. I guess it's because Row1 and ColumnA are splited and frozen pane. Anything that we could do in conditional formatting or even VBA? Thanks in advance Toto Sanderson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Cell Color when another cell is selected
http://cjoint.com/?fDvhRg4sDJ -Select A2:D16 -Format/Conditiannal formatting =Row()=Cell("row") =Column()=Cell("Column") Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calculate End Sub Cordially JB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
how read value from last selected cell? It is possible? how get adress last selected cell? | New Users to Excel | |||
Perform oiperations relative to initial selected cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
How can I change which cell is selected next, other than Down, Le. | Excel Discussion (Misc queries) |