Change background colour of selected cells

[Redleg]

I wish to change the background colour of any selected cell on entry and
revert to original colour (none) on exit.

 

[Redleg]

Thanks for the help so far Greg.

In both options I get a runtime error "Unable to set the ColorIndex property
of the interior class"

 

[Greg Wilson]

The worksheet is presumably protected. The following code unprotects, does
the job, then reprotects. If you are not using a password then you can delete
the password argument that follows the unprotect and protect statements. If
you are using a password then you need to substitute it for "mypassword":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Unprotect "mypassword"
Cells.FormatConditions.Delete
If Len(copyval) > 0 Then CopyToClip (copyval)
With Target
If .Count > 1 Then Exit Sub
copyval = .Value
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 19
End With
Me.Protect "mypassword"
End Sub

Alternatively, if you are using xl2003 or later, there is probably
protection option that allows conditional formating changes thus letting you
avoid the above changes. I don't have access to xl2003 at this time to check
this out.

Regards,
Greg

 

[Greg Wilson]

I suggest you substitute the following. The problem with the previous version
is that it will protect the wks even if it wasn't protected to begin with.
This one will only protect it if it was already protected. Otherwise it could
be a nuisance depending on what you are doing. The same comments hold
regarding the password.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim protected As Boolean
protected = Me.ProtectContents
If protected Then Me.Unprotect ' "mypassword"
Cells.FormatConditions.Delete
If Len(copyval) > 0 Then CopyToClip (copyval)
With Target
If .Count > 1 Then Exit Sub
copyval = .Value
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 19
End With
If protected Then Me.Protect ' "mypassword"
End Sub

Greg

 

[Redleg]

Great stuff Greg. Far more elegant than my attempts.
Protected was not reset if .Count > 1.
Fixed that but removed the test in the end because I have some merged cells.

Thanks for your solution,

Redleg