Change Cell Color when another cell is selected

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Ardus Petus]

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
'----------------------------------------

 

[JB]

http://cjoint.com/?fDvhRg4sDJ

-Select A216
-Format/Conditiannal formatting
=Row()=Cell("row")
=Column()=Cell("Column")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

Cordially JB