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
--
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 Removed)
"Ken Hudson" <(E-Mail Removed)> wrote in message
news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
>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
> --
> Ken Hudson

On Aug 6, 3:10 pm, Ken Hudson <KenHud...@discussions.microsoft.com>
wrote:
> 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
> --
> Ken Hudson

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

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

On Mon, 6 Aug 2007 15:10:02 -0700, Ken Hudson
<(E-Mail Removed)> wrote:

>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

Perfect!
Thanks Don.
--
Ken Hudson


"Don Guillett" wrote:

> 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 Removed)
> "Ken Hudson" <(E-Mail Removed)> wrote in message
> news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
> >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
> > --
> > Ken Hudson
>
>

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Ken Hudson" <(E-Mail Removed)> wrote in message
news:0D190FA5-2619-4484-9FA9-(E-Mail Removed)...
> Perfect!
> Thanks Don.
> --
> Ken Hudson
>
>
> "Don Guillett" wrote:
>
>> 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 Removed)
>> "Ken Hudson" <(E-Mail Removed)> wrote in message
>> news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
>> >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
>> > --
>> > 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?
--
Ken Hudson


"Don Guillett" wrote:

> Glad to help
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Ken Hudson" <(E-Mail Removed)> wrote in message
> news:0D190FA5-2619-4484-9FA9-(E-Mail Removed)...
> > Perfect!
> > Thanks Don.
> > --
> > Ken Hudson
> >
> >
> > "Don Guillett" wrote:
> >
> >> 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 Removed)
> >> "Ken Hudson" <(E-Mail Removed)> wrote in message
> >> news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
> >> >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
> >> > --
> >> > Ken Hudson
> >>
> >>
>
>

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.

On Aug 7, 8:20 am, Ken Hudson <KenHud...@discussions.microsoft.com>
wrote:
> 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?
> --
> Ken Hudson
>
> "Don Guillett" wrote:
> > Glad to help
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com
> > "Ken Hudson" <KenHud...@discussions.microsoft.com> wrote in message
> >news:0D190FA5-2619-4484-9FA9-(E-Mail Removed)...
> > > Perfect!
> > > Thanks Don.
> > > --
> > > Ken Hudson
>
> > > "Don Guillett" wrote:
>
> > >> 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
> > >> dguille...@austin.rr.com
> > >> "Ken Hudson" <KenHud...@discussions.microsoft.com> wrote in message
> > >>news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
> > >> >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
> > >> > --
> > >> > Ken Hudson

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

On Aug 7, 10:36 am, Ferris <kochj...@gmail.com> wrote:
> 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.
>
> On Aug 7, 8:20 am, Ken Hudson <KenHud...@discussions.microsoft.com>
> wrote:
>
> > 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?
> > --
> > Ken Hudson
>
> > "Don Guillett" wrote:
> > > Glad to help
>
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > dguille...@austin.rr.com
> > > "Ken Hudson" <KenHud...@discussions.microsoft.com> wrote in message
> > >news:0D190FA5-2619-4484-9FA9-(E-Mail Removed)...
> > > > Perfect!
> > > > Thanks Don.
> > > > --
> > > > Ken Hudson
>
> > > > "Don Guillett" wrote:
>
> > > >> 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
> > > >> dguille...@austin.rr.com
> > > >> "Ken Hudson" <KenHud...@discussions.microsoft.com> wrote in message
> > > >>news:517055D2-AC79-457F-BC60-(E-Mail Removed)...
> > > >> >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
> > > >> > --
> > > >> > Ken Hudson