Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
On Aug 6, 3:10 pm, Ken Hudson
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 "Ken Hudson" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Ken Hudson" wrote in message ... 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 "Ken Hudson" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 "Ken Hudson" wrote in message ... 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 "Ken Hudson" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 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 "Ken Hudson" wrote in message ... 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 "Ken Hudson" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Interior Cell Color
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 B2:D10 you could add some code like this: Set isect = Intersect(Target.Range, Worksheets("Sheet1").Range("B2:D10) ) 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 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 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 "Ken Hudson" wrote in message ... 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 "Ken Hudson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the default interior color palette index in Excel 2003 | Excel Programming | |||
Cell background color (interior color) setting not working | Excel Programming | |||
Change the interior color of a cell - Code Review | Excel Programming | |||
interior color of a cell | Excel Programming |