Verify Tool

Posted by Bob J on October 23, 2001 10:44 AM

I would like to temporarily color a cell, if the cell is active, and then when it is not active
Go back to its original color:

Verify Sub()
Activecell.select
With selection.interior
.colorindex = 6
End with
End sub

But once it is out of the activecell go back to Colorindex of 0
Thanks..bj

Posted by Barrie Davidson on October 23, 2001 11:04 AM

BJ, try inserting this code in the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = -4142
With Target.Interior
.ColorIndex = 6
End With
End Sub

Regards,
Barrie (neat code to implement)Barrie Davidson

Posted by Bob J on October 23, 2001 11:10 AM

Thanks, I have a pretty wide spreadsheet that users view daily.
This will help them see straight across! I will probably change
the activecell, to Activecell.row Thanks Again...

Posted by Barrie Davidson on October 23, 2001 11:14 AM

In that case..

Change the code to read:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = -4142
With Target.EntireRow.Interior
.ColorIndex = 6
End With
End Sub

BarrieBarrie Davidson

Posted by Bob J on October 23, 2001 11:27 AM

Re: In that case..

Great! What if I wanted to eliminate Columns 11 & 12, and All of row 1?
Sounds like you have done this before? Bj

Posted by Barrie Davidson on October 23, 2001 11:35 AM

Re: In that case..

Change your code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = -4142
If Target.Column = 11 Or Target.Column = 12 Or Target.Row = 1 Then Exit Sub
With Target.EntireRow.Interior
.ColorIndex = 6
End With
End Sub

Nope, never done this before but it was interesting.

Barrie :)
Barrie Davidson

Posted by Bob J on October 23, 2001 12:18 PM

I love VBA....