How Do you identify Activecell for Conditional Formatting? 1

[scottmh]

I have a 2 dimensional array and I would like to have the column and row header cells change color when the intersecting cell is selected.

I've been trying to build it as a Function that I put in the "Formula is" selecion of the conditional formatting, but, I can't seem to get it right.

Here is my code....

Function Selected(rCell As Range) As Boolean
Dim rC As Range
Dim bool As Boolean

Set rC = rCell.Cells

For Each rC In rCell
If rC.Address = ActiveCell.Address Then
Selected = True
Exit For
Else
Selected = False
End If
Next

End Function

Thanks,
Scott

 

[combo]

I would rather try this way:
1. Name the data region (without headers and left side description), say, TestData,
2. Add names: xRow and xCol, procedure AddNames below does it locally,
3. Format conditionally header row, with formula to all cells =(AND(xRow<>0,COLUMN()=xCol))
4. Format conditionally left (description) column, with formula to all cells =(AND(xCol<>0,ROW()=xRow))
5. In the worksheet module add code (event procedure uses local names here):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("TestRange")) Is Nothing Then
    Me.Names("xRow").RefersTo = "=" & Target.Row
    Me.Names("xCol").RefersTo = "=" & Target.Column
Else
    Me.Names("xRow").RefersTo = "=0"
    Me.Names("xCol").RefersTo = "=0"
End If
End Sub

Sub AddNames()
With Me
    .Names.Add Name:="xRow", RefersTo:="=0"
    .Names.Add Name:="xCol", RefersTo:="=0"
End With
End Sub

combo

 

[scottmh]

Thanks, Combo. However, I found an Add-in that better helps me identify the activecell to show me what row and column I'm on. Rowliner from cpearson.com . My end result was to highlight the x and y title cells so I know I'm on the correct cell to enter/change the data. The other item is that I want to use this on multiple sheets in a workbook and in other workbooks.

I appreciate your quick reply and will be playing with your code on some other projects