XL2K - Conditional Formatting relative to cell selection

Is there a way I can change the colour of an array of cells depending on whether a different specified cell in the worksheet has been selected?

For example, the conditional formula in each cell in the range X1:Z3 would effectively be: 'if A1 is the currently selected cell, turn me green'.

Thanks - Lez

Enter this macro:

Sub Macro1()
Dim r As Range
Set r = Range("A1")
If Not Intersect(r, Selection) Is Nothing Then
Range("X1:Z3").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub


If you select A1 and then run the macro, the other cells will be colored.
--
Gary's Student


"Lezh" wrote:

>
> Is there a way I can change the colour of an array of cells depending on
> whether a different specified cell in the worksheet has been selected?
>
> For example, the conditional formula in each cell in the range X1:Z3
> would effectively be: 'if A1 is the currently selected cell, turn me
> green'.
>
> Thanks - Lez
>
>
> --
> Lezh
> ------------------------------------------------------------------------
> Lezh's Profile: http://www.excelforum.com/member.php...o&userid=29962
> View this thread: http://www.excelforum.com/showthread...hreadid=496600
>
>

Create this UDF:
Public Function AktCell() As String
Application.Volatile
AktCell = ActiveCell.Address(False, False)
End Function

Create this event procedure:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

Apply Conditional formatting in the range X1:Z3
formula: =AktCell()="A1"
and choose green background!

Regards,
Stefi



„Lezh” ezt �*rta:

>
> Is there a way I can change the colour of an array of cells depending on
> whether a different specified cell in the worksheet has been selected?
>
> For example, the conditional formula in each cell in the range X1:Z3
> would effectively be: 'if A1 is the currently selected cell, turn me
> green'.
>
> Thanks - Lez
>
>
> --
> Lezh
> ------------------------------------------------------------------------
> Lezh's Profile: http://www.excelforum.com/member.php...o&userid=29962
> View this thread: http://www.excelforum.com/showthread...hreadid=496600
>
>

Thanks GS and Stefi for your input. Although my xl experience has been limited to the commoner formulas up till now (though I recorded a macro once and it worked!) I'll try your solutions as a cure for the new year hangover at the weekend - but I think I might just have to read up a bit on how to enter this stuff!

Much appreciate your help. Thanks again.