current row /col shading (Excel 2002)

[soonah98]

Hi, does anyone know if it is possible to shade the current row and column to make it easier to input data? I have names on the left of the screen and am inputting numbers on the right so it is easy to make a mistake and input data in the wrong row.

I know excel highlights the rol and col numbers as the cursor is moved, but this is not strong enough. I'd prefer the whole row to be highlighted as I move.

Any thoughts? Cheers, Mike C.

[sdckapr]

No built in way:

Try this <img src=/S/free.gif border=0 alt=free width=30 height=15> addin from Chip Pearson

Steve

[chipshot]

You could also try: <!post=this code,307217>this code<!/post>

[sdckapr]

I couldn't find the post so couldn't reference it. Thanks.

One thing to be aware of with your code, is that you "erase" any cell formatting that is currently in the cell. Chip's method will not.

Steve

[Jim Cone]

Try... Data Menu | Form

[Michael Evans]

Another possiblity is to colour alternate rows. The following macro does this (most of the code is to avoid losing the cell grey borders):

Sub AltRowsGreenGreyBorders()
'
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 35
'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
If Selection.Columns.Count > 1 Then
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If
If Selection.Rows.Count > 1 Then
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If

End Sub

The following macro clears the colouring:

Sub ClearFormats()
'
Selection.ClearFormats
End Sub

[GoCush]

Mike

Another possible way to approach this:
Since some of the difficulty is trying to match cells on the right of the screen with cells on the left, you may want to TEMPORARILY HIDE the intervening columns - Format | Columns | Hide
After entering data then UNHIDE the subject columns.
Simple macros can also do this.
This is a low-tech method but sometimes that's the best.

[BilasTasol]

Mike:

Another 'low-tech' solution is to keep the names column on the screen when scrolling right, thus letting you move the numbers column(s) next to the names.

Click in the first cell of the column to the right of the names column and select Window/Freeze Panes.

Tony.