Change Interior Cell Color

[Ken Hudson]

I have a worksheet whose cells have different interior colors. When I select
a cell, I want to change the interior color to 35. When I move on to another
cell, I want to change that color back to its original color.
I found the following code to make the intial change, but haven't figured
out how to return the original color.
Thanks for helping.

---------------------------------

Option Explicit
Dim OldActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If OldActiveCell Is Nothing Then
Set OldActiveCell = Target
Else
OldActiveCell.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 35
Set OldActiveCell = Target
End Sub

 

[Don Guillett]

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 35
End With
End Sub

 

[Ferris]

I have a worksheet whose cells have different interior colors. When I select
a cell, I want to change the interior color to 35. When I move on to another
cell, I want to change that color back to its original color.
I found the following code to make the intial change, but haven't figured
out how to return the original color.
Thanks for helping.

---------------------------------

Option Explicit
Dim OldActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If OldActiveCell Is Nothing Then
Set OldActiveCell = Target
Else
OldActiveCell.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 35
Set OldActiveCell = Target
End Sub

Give this a try, it will blow away any conditional formatting you
already have in place, but it essentially uses conditional formatting
to highlight the cell. That way when you move to a different cell it
just deletes the conditional formatting leaving the original fill:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
iColor = 36
Cells.FormatConditions.Delete
With Range(Target.Address)
.FormatConditions.Add Type:=2, Formula1:=1
.FormatConditions(1).Interior.ColorIndex = iColor
End With
End Sub

 

[Gord Dibben]

Ken

This code from Don Guillett will do the trick.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 20
End With
End Sub


Gord Dibben MS Excel MVP

 

[Ken Hudson]

Perfect!
Thanks Don.
--
Ken Hudson

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 35
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help

 

[Ken Hudson]

I always ttry to undersand what the code actually does. In this case you are
using conditional formatting to accomplish the goal? First you delete all
existing conditional formatting from the sheet, re-setting the formats to
their original state. If I had some other conditonal formatting on the sheet
(I don't), it would be deleted and I'd have to re-code it.
Then the With/End With sets the interior color of the active range.
Is this the general operation?

 

[Ferris]

Yes, that is the general operation - conditional formatting is used to
accomplish the goal. However, the code will always delete all
conditional formatting in your sheet whenever you change cells - so
there's no way to add conditional formatting to a sheet using this
method to highlight cells.

 

[Ferris]

Sorry, mis-spoke. You could have other conditional formatting on the
sheet as long as you never click on it. You could use the Intersect
method to fine tune where on your sheet the highlighting code will
run. For example, if you wanted to have the code only highlight cells
in the range B210 you could add some code like this:

Set isect = Intersect(Target.Range,
Worksheets("Sheet1").Range("B210) )
If Not isect Is Nothing Then
'add the code to highlight the cell
End If

You may need to tweak this a bit to suit your needs. The Intersect
method checks to see if the cell you've activated is within a defined
range. Somebody else may have a more elegant way of doing this..