Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

A Useful MS Excel Macro

Apr 5, 2005 12:42PM PDT

I use Excel (2002 - SP3) to maintain the membership list of a 650-member organization. When I have to edit the entries, to avoid making changes on the wrong line, I set the background color of the current row of interest to yellow. Moving on to the next line means erasing the yellow on the current line and yellowing the next line -- kind of clunky. So I wrote a macro to capture these actions, but it didn't work properly. Here's why: Let R be the number of the yellowed row, and let N = R+1. The macro embedded these two numbers literally, and was thus useless for moving on beyond these two rows. I found a way to fix the macro so it would work properly. I have pasted it in below, showing the lines that had to be added and modified.

Sub RoMark()
'
' RoMark Macro
' Macro recorded 4/5/2005 by PDRat
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
Dim Row As Integer, Nxt As Integer 'Added by PDRat

Row = Selection.Row 'Added by PDRat
Nxt = Row + 1 'Added by PDRat

Selection.Copy
' Rows("8:8").Select 'Original
Rows(Nxt).Select 'Modified by PDRat
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' Rows("7:7").Select 'Original
Rows(Row).Select 'Modified by PDRat
Selection.Interior.ColorIndex = xlNone
End Sub

Discussion is locked