Activecell change 2

[Clarkie001]

Hi,

I have the following code which works fine for what I want, but when I select another cell within the range of C1 to C8 I need the previous cells Interior to change back to xlnone.

Can anyone shed any light on this?....Thanks in advance.

Clarkie

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell >= Range(&quot;C1&quot And ActiveCell <= Range(&quot;C8&quot Then

With ActiveCell.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With

Range(&quot;A1&quot.Value = ActiveCell
Else
DoEvents
End If

End Sub

 

[tbl]

Just use TARGET = any range of your choice and you can call any macro depending on the value of TARGET at that point in your program

Richard

 

[Bowers74]

This should help you out:

Option Explicit
Public PrevTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

If ActiveCell.Column = 3 And ActiveCell.Row >= 1 And _
    ActiveCell.Row <= 8 Then
    
    If PrevTarget.Column = 3 And PrevTarget.Row >= 1 And _
    PrevTarget.Row <= 8 Then
        PrevTarget.Interior.ColorIndex = xlNone
    End If
    
    Target.Interior.ColorIndex = 41
    
Range(&quot;A1&quot;).Value = ActiveCell.Address
Else
DoEvents
End If

Set PrevTarget = Nothing
Set PrevTarget = ActiveCell

End Sub

I created a public variable &quot;PrevTarget&quot; that will allow Excel to &quot;remember&quot; the previous cell that was selected. You might want to tweek it a bit to get it to do EXACTLY what you want ;-)

Enjoy!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

tbl makes a good point, but I tend to use the Public variable just in case I want to use Target for something else later within the event.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[tbl]

The last code is not foolproof as it doesn't work going from bottom to top.This seems to work in all circumstances.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set CurrentCell = ActiveCell
Set isect = Application.Intersect(Range(ActiveCell.Address), Range(&quot;C1:C8&quot)
If Not isect Is Nothing Then
Range(&quot;C1:C7&quot.Interior.ColorIndex = xlNone
With CurrentCell.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Range(&quot;A1&quot.Value = ActiveCell.Address
Else
DoEvents
End If

End Sub

 

[Loomah]

I know this seems to have been sorted but you could also use the sledgehammer approach:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C1:C8")) Is Nothing Then
    Range("C1:C8").Interior.ColorIndex = xlNone
    Target.Interior.ColorIndex = 41
Range("A1").Value = ActiveCell
Else
DoEvents
End If
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244