Change Interior Cell Color

K

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
 
D

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
 
F

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
 
G

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
 
K

Ken Hudson

Perfect!
Thanks Don.
--
Ken Hudson


Don Guillett said:
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)
 
K

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?
 
F

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.
 
F

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 B2:D10 you could add some code like this:

Set isect = Intersect(Target.Range,
Worksheets("Sheet1").Range("B2:D10) )
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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top