MrExcel Publishing
Your One Stop for Excel Tips & Solutions

cell reference headers


Posted by Scott Tunis on November 29, 2000 10:49 AM

I hope this doesn't fall into the "Can you total that diagonally for me?" category, but here goes.

I use some fairly large worksheets that are used to track item allocation to various locations.
The first row and column are used to name the location and item.

For example when I am inputting info for "Windsor Branch" the data goes into column CN. If the item being recorded is a "Quick Draw Spigot" that info is placed in row 245.

The cell reference for the example is CN245.

The locations and items are frozen by splitting and freezing so that when scrolling to the farther intersecting cells the location and item names are visible.

My question: Is there a way to make the row and column headers more distinct ? The type in the active row and column headers is bolded, but I was hoping to make a more noticeable change.
Text color or button color for example. I know that the current pointer location is shown by the status bar, but it would help me if the headers themselves were made more visible.

I would like to be able to confirm at a glance that I am in the cell that corresponds to the intersection of location "Windsor Branch" and item "Quick Draw Spigot"


Posted by Tim Francis-Wright on November 29, 2000 11:51 AM


Here is something I adapted from an item
on Chip Pearson's site (www.cpearson.com/excel.htm):

Put this in the particular sheet object in the VBA editor. It highlights the cell that you are in,
as well as the corresponding "edge cells";
so, CN245, A245, and CN1 are highlighted.
You could modify this to change the highlighting
color, or to change the font color instead.

HTH!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static OldRange As Range
Static OldRow As Range
Static OldCol As Range
Dim NewRow As Range
Dim NewCol As Range

On Error Resume Next
Set NewRow = Intersect(Target.EntireRow, ActiveSheet.Range("a1:a65535"))
Set NewCol = Intersect(Target.EntireColumn, ActiveSheet.Range("a1:iv1"))

OldRange.Interior.ColorIndex = xlColorIndexNone
OldRow.Interior.ColorIndex = xlColorIndexNone
OldCol.Interior.ColorIndex = xlColorIndexNone
Target.Interior.ColorIndex = 6 ' yellow - change s needed
NewRow.Interior.ColorIndex = 6
NewCol.Interior.ColorIndex = 6
Set OldRange = Target
Set OldRow = NewRow
Set OldCol = NewCol

End Sub