highlighting rows and columns when selecting a cell - how?

Posted by Sam E. on August 13, 2001 9:46 AM

I created an Excel gradebook but would like to add a convienent feature. I want the entire row and column to be highlighted when I select a cell. For example, when I click a cell to type a grade, I want the assignment heading at the top (column) and the student's name (row) to light up so I know that I am inputting the grade in the correct spot.

Thanks

Posted by Mark W. on August 13, 2001 10:15 AM

1. Select cell A1
2. Choose the Insert | Name | Define... menu command
3. Type something like "My_Selection" in the
"Names in workbook" entry field and...
=Sheet2!A:A,Sheet2!1:1,Sheet2!A1 in the "Refers to"
field.
4. Press [ OK ]

Now select any cell (e.g., E5), press Ctrl+G, and
choose My_Selection from Go To list.

Posted by lenze on August 13, 2001 10:17 AM

You can use Shift-Space to highlight the row of the active cell and Conrol space to highlight the active column, but this is a clumsy approach. Have you considered using the built in Data Form option in Criteria mode to add and edit records? It really works well for this type of application

Posted by faster on August 13, 2001 1:34 PM

This code will toggle colors on your sheet.
As is it will remove all existing colors, so if
you want to keep existing colors you will need to
adjust the code.

Sub ColorIt()
'toggles worksheet colors
'code will remove all color
'and color active row and column

If ActiveCell.Interior.ColorIndex &LT;&GT; xlNone Then
Cells.Interior.ColorIndex = xlNone
Else
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
ActiveCell.EntireRow.Interior.ColorIndex = 15
End If
End Sub

Posted by SamE on August 14, 2001 7:28 AM

I tried this out, but I had to run the macro every time I selected a cell. How do you make it run all the time and how can I keep the original colors?

Sam

Posted by faster on August 14, 2001 10:10 AM

I wasn't able to find a way to do this exactly like
you needed. You would think it would be possible.

Posted by faster on August 14, 2001 12:52 PM

One last shot. . .

Here is a last attempt at doing what you need:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'right click the tab of the sheet you are working in
'and select view code
'paste this code into the sheet
'repeat for mutiple sheets
'this works but existing color formats are lost

Static LastChange
Application.ScreenUpdating = False

If LastChange = Empty Then
LastChange = ActiveCell.Address
End If

Range(LastChange).EntireColumn.Interior.ColorIndex = xlNone
Range(LastChange).EntireRow.Interior.ColorIndex = xlNone

ActiveCell.EntireColumn.Interior.ColorIndex = 15
ActiveCell.EntireRow.Interior.ColorIndex = 15

LastChange = ActiveCell.Address

Application.ScreenUpdating = True

End Sub

'***************************************************
Private Sub Workbook_Open()
'paste this code into the ThisWorkbook module
Application.ScreenUpdating = False

Dim MyStart
MyStart = ActiveCell.Address
Range("A1").Select
Range("B4").Select
Range("C9").Select
Range(MyStart).Select

Application.ScreenUpdating = True
End Sub

Posted by Ian on August 14, 2001 3:39 PM

Re: One last shot. . .With a twist!!

Fasters code works in the SheetSelectionChange event:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'toggles worksheet colors
'code will remove all color
'and color active row and column

End Sub

Ian

Posted by Sam on August 14, 2001 10:28 PM

Thanks anyway! It should be possible. it seems logical to me anyway. I wasn't able to find a way to do this exactly like

Posted by Ivan F Moala on August 15, 2001 12:00 AM

Sam
If no joy then try this.
Works on the sheet selection change event.
Notes:
1) Assumes Row titles A1:G12
2) Column Tiles A2:A12
(see ranges as defined in macro - change here)

What this routine does is to highlight from the
selected cell vertically to th title @ top AND
Horizontally to the left Titles. Colour = yellow.

If it doesn't make sence then I'll email it

Ivan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oRow As String
Dim oCol As String
Dim DataRg As Range
Dim A As Range

Dim AllRg As Range

Set DataRg = Range("B2:G12")
Set AllRg = Range("A1:G12")

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Target, DataRg) Is Nothing Then
AllRg.Interior.ColorIndex = xlNone
oRow = "A" & Target.Row & ":" & Target.Offset(0, -1).Address
oCol = Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address
Range(oRow).Interior.ColorIndex = 6
Range(oCol).Interior.ColorIndex = 6
Else
AllRg.Interior.ColorIndex = xlNone
End If

End Sub