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 -- Lezh ------------------------------------------------------------------------ Lezh's Profile: http://www.excelforum.com/member.php...o&userid=29962 View this thread: http://www.excelforum.com/showthread...hreadid=496600 |
XL2K - Conditional Formatting relative to cell selection
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 |
XL2K - Conditional Formatting relative to cell selection
Create this UDF:
Public Function AktCell() As String Application.Volatile AktCell = ActiveCell.Address(False, False) End Function Create this event procedu 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 |
XL2K - Conditional Formatting relative to cell selection
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! :confused: :( Much appreciate your help. Thanks again. :) -- Lezh ------------------------------------------------------------------------ Lezh's Profile: http://www.excelforum.com/member.php...o&userid=29962 View this thread: http://www.excelforum.com/showthread...hreadid=496600 |
All times are GMT +1. The time now is 06:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com