|
Hi-Lited Cell trouble
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 -- Brian Matlac ----------------------------------------------------------------------- Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350 View this thread: http://www.excelforum.com/showthread.php?threadid=53678 |
Hi-Lited Cell trouble
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 -- Regards, Tom Ogilvy "Brian Matlack" wrote: Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
Hi-Lited Cell trouble
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" a écrit dans le message de news:Brian.Matlack.26xd7y_1146144602.2854@excelfor um-nospam.com... Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
Hi-Lited Cell trouble
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" a écrit dans le message de news:Brian.Matlack.26xd7y_1146144602.2854@excelfor um-nospam.com... Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
Hi-Lited Cell trouble
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" a écrit dans le message de news:Brian.Matlack.26xd7y_1146144602.2854@excelfor um-nospam.com... Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
Hi-Lited Cell trouble
Tom Ogilvy Wrote: 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 -- Regards, Tom Ogilvy "Brian Matlack" wrote: Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 Thanks Tom! I'll try it. -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
Hi-Lited Cell trouble
Ardus Petus Wrote: 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" a écrit dans le message de news:Brian.Matlack.26xd7y_1146144602.2854@excelfor um-nospam.com... Hi! I got this code off of one of Chip Pearsons sites and it works great on 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 to another cell the Red cell is now White. I want the cells to revert back to whatever their original color was. 2. Can I effect the Font Color as well as the background color and still have them revert back to the original settings. <start code Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal 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! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 Thanks Ardus! It works Great! -- Brian Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=536781 |
| All times are GMT +1. The time now is 01:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com