COLOUR ACTIVE CELL

[verluc]

Have somebody a macro,which can give a colour to the active cell?
Is this possible in "This workbook"
Many thanks

 

[rikrak]

There is a solution:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Unprotect Password:="yourPassword"

Range("A1:Z20").Interior.Pattern = xlSolid
ActiveCell.Interior.Pattern = xlSolid

ActiveSheet.Protect Password:="yourPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[verluc]

On 2002-08-26 13:58, rikrak wrote:
There is a solution:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Unprotect Password:="yourPassword"

Range("A1:Z20").Interior.Pattern = xlSolid
ActiveCell.Interior.Pattern = xlSolid

ActiveSheet.Protect Password:="yourPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Hi, you are great.It works but I get no colour in my cell.
Any idea?

 

[rikrak]

sorry Verluc, wrong lines. I was already trying something new. Here is what I wanted to send:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="yourPassword"
Range("A1:Z20").Interior.Pattern = xlSolid
ActiveCell.Interior.Pattern = xlGray16
ActiveSheet.Protect Password:="yourPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

It gives the active cell a pattern.

 

[verluc]

On 2002-08-26 14:26, rikrak wrote:
sorry Verluc, wrong lines. I was already trying something new. Here is what I wanted to send:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="yourPassword"
Range("A1:Z20").Interior.Pattern = xlSolid
ActiveCell.Interior.Pattern = xlGray16
ActiveSheet.Protect Password:="yourPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

It gives the active cell a pattern.

Sorry,a last question.
Is it possible to make it in full red color?
Thanks

 

[rikrak]

I'm working on it.
Something with conditional format and a user defined function:
Function isActiveCell(cell As Range) As Boolean
isActiveCell = (ActiveCell.Address = cell.Address)
End Function

The problem is: how to force the conditional format to update.

Maybe tomorrow, it's bedtime now (24:00)
This message was edited by rikrak on 2002-08-26 14:59

 

[rikrak]

Hi verluc, try this.

1) Add following to a new or existing module:

Function isActiveCell(cell As Range) As Boolean
isActiveCell = (ActiveCell.Address = cell.Address)
End Function

2) In the worksheet select the area where the active cell has to be colored and
apply a conditional format:

formula is =isActiveCell('address of cell')

When you selected "A1:K100" type: formula is =isActiveCell(A1)
Select a fillcolor as pattern.

The result has to be:
A1: formula is =isActiveCell(A1)
B1: formula is =isActiveCell(B1)
...
K100: formula is =isActiveCell(K100)


3) Then add the last line of code into the worksheets "selectionChange" event:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Range("A1:K100").Calculate
End Sub

(Range "A1:K100" is the same range as in 2)