Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Color banding to locate active cells
How do I do this? This is per Office Help, but I am lost on how to accomplish
this. The instructions a This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column. '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Color banding to locate active cells
DA, this should do it, you may also want to have a look at Chip Pearson's
row liner addin at http://www.cpearson.com/excel/rowliner.htm Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'will highlight the current row and column 'Note: Don't use IF you have Conditional formatting that you want to keep! Dim iColor As Integer '// On error resume in case '// user selects a range of cells On Error Resume Next iColor = Target.Interior.ColorIndex 'Leave On Error ON for Row offset errors If iColor < 0 Then iColor = 36 Else iColor = iColor + 1 End If '// Need this test incase Font color is the same If iColor = Target.Font.ColorIndex Then iColor = iColor + 1 Cells.FormatConditions.Delete '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With '// Vertical color banding With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DA" wrote in message ... How do I do this? This is per Office Help, but I am lost on how to accomplish this. The instructions a This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column. '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Color banding to locate active cells
Thanks for the info, but I have this. I am Excel dumb and what I don't know
is how to apply this information to make this work. I guess I need the actual steps and where to enter this stuff. Any idea? "Paul B" wrote: DA, this should do it, you may also want to have a look at Chip Pearson's row liner addin at http://www.cpearson.com/excel/rowliner.htm Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'will highlight the current row and column 'Note: Don't use IF you have Conditional formatting that you want to keep! Dim iColor As Integer '// On error resume in case '// user selects a range of cells On Error Resume Next iColor = Target.Interior.ColorIndex 'Leave On Error ON for Row offset errors If iColor < 0 Then iColor = 36 Else iColor = iColor + 1 End If '// Need this test incase Font color is the same If iColor = Target.Font.ColorIndex Then iColor = iColor + 1 Cells.FormatConditions.Delete '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With '// Vertical color banding With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DA" wrote in message ... How do I do this? This is per Office Help, but I am lost on how to accomplish this. The instructions a This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column. '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Color banding to locate active cells
DA,
To put in this code right click on the worksheet tab and view code, in the window that opens paste this code, press Alt and Q to close this window and go back to your workbook. If you are using excel 2000 or newer you may have to change the macro security settings to get the macro to run. To change the security settings go to tools, macro, security, security level and set it to medium -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DA" wrote in message ... Thanks for the info, but I have this. I am Excel dumb and what I don't know is how to apply this information to make this work. I guess I need the actual steps and where to enter this stuff. Any idea? "Paul B" wrote: DA, this should do it, you may also want to have a look at Chip Pearson's row liner addin at http://www.cpearson.com/excel/rowliner.htm Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'will highlight the current row and column 'Note: Don't use IF you have Conditional formatting that you want to keep! Dim iColor As Integer '// On error resume in case '// user selects a range of cells On Error Resume Next iColor = Target.Interior.ColorIndex 'Leave On Error ON for Row offset errors If iColor < 0 Then iColor = 36 Else iColor = iColor + 1 End If '// Need this test incase Font color is the same If iColor = Target.Font.ColorIndex Then iColor = iColor + 1 Cells.FormatConditions.Delete '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With '// Vertical color banding With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DA" wrote in message ... How do I do this? This is per Office Help, but I am lost on how to accomplish this. The instructions a This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column. '// Horizontal color banding With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to scroll through active cells instead of entire worksheet | Excel Discussion (Misc queries) | |||
Marking unlocked cells in active worksheet | Excel Discussion (Misc queries) | |||
Clearing all Active Cells | Excel Worksheet Functions | |||
Counting only active cells | Excel Discussion (Misc queries) | |||
Locate and delete specific cells | Excel Discussion (Misc queries) |