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

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

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

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