Hi-Lited Cell trouble

[Brian Matlack]

Hi!
I got this code off of one of Chip Pearsons sites and it works great o
a white sheet.
Is there a way to modify it so that:

1. If I have a cell colored Red and I select it and then move on t
another cell the Red cell is now White. I want the cells to revert bac
to whatever their original color was.

2. Can I effect the Font Color as well as the background color an
still have them revert back to the original settings.

<start code>
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVa
Target As Excel.Range)
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = 6 'Yellow

Set OldCell = Target

End Sub
<end code>
Thanks for your time and help

 

[Tom Ogilvy]

Instead of changing the color index of the cell, have your code apply a
conditional format to the cell. this allows you to change the font color
and background color without altering the original colors.

You can turn on the macro recorder while you apply a conditional format
manually to get the code. The formula just needs to return true, so use
Formula is and =True

 

[Ardus Petus]

Here is some code.
BTW, I'm not quite sure your code (as well as mine) works properly if you
switch between several Worksheets.

HTH
--
AP

'---------
Option Explicit

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)

Static OldCell As Range
Static OldFontColor As Long
Static OldBackColor As Long

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = OldBackColor
OldCell.Font.ColorIndex = OldFontColor
End If

Set OldCell = Target
OldBackColor = Target.Interior.ColorIndex
OldFontColor = Target.Font.ColorIndex

Target.Interior.ColorIndex = 6 'Yellow


End Sub
'------------
"Brian Matlack" <[email protected]>
a écrit dans le message de
news:[email protected]...

 

[Ardus Petus]

I made a minor correction (Activecell instead of Target) to cope with errors
that occured when selecting multiple cells with different colors.

HTH
--
AP

'-------
Option Explicit

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)

Static OldCell As Range
Static OldFontColor As Long
Static OldBackColor As Long

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = OldBackColor
OldCell.Font.ColorIndex = OldFontColor
End If

Set OldCell = ActiveCell
OldBackColor = ActiveCell.Interior.ColorIndex
OldFontColor = ActiveCell.Font.ColorIndex

Target.Interior.ColorIndex = 6 'Yellow


End Sub
'-------

"Brian Matlack" <[email protected]>
a écrit dans le message de
news:[email protected]...

 

[Ardus Petus]

Ooops: forgot one Target

'-------
Option Explicit

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)

Static OldCell As Range
Static OldFontColor As Long
Static OldBackColor As Long

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = OldBackColor
OldCell.Font.ColorIndex = OldFontColor
End If

Set OldCell = ActiveCell
OldBackColor = ActiveCell.Interior.ColorIndex
OldFontColor = ActiveCell.Font.ColorIndex

ActiveCell.Interior.ColorIndex = 6 'Yellow


End Sub
'---------

"Brian Matlack" <[email protected]>
a écrit dans le message de
news:[email protected]...

 

[Brian Matlack]

Instead of changing the color index of the cell, have your code apply a
conditional format to the cell. this allows you to change the font
color
and background color without altering the original colors.

You can turn on the macro recorder while you apply a conditional
format
manually to get the code. The formula just needs to return true, so
use
Formula is and =True

Thanks Tom! I'll try it.

 

[Brian Matlack]

I made a minor correction (Activecell instead of Target) to cope with
errors
that occured when selecting multiple cells with different colors.

HTH
--
AP

'-------
Option Explicit

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)

Static OldCell As Range
Static OldFontColor As Long
Static OldBackColor As Long

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = OldBackColor
OldCell.Font.ColorIndex = OldFontColor
End If

Set OldCell = ActiveCell
OldBackColor = ActiveCell.Interior.ColorIndex
OldFontColor = ActiveCell.Font.ColorIndex

Target.Interior.ColorIndex = 6 'Yellow


End Sub
'-------

"Brian Matlack"
<[email protected]>
a écrit dans le message de

Thanks Ardus! It works Great!