"JeffH" wrote in message
...
: HI,
:
: I don't know much about Excel Macros but I'm trying to learn this as best
I
: can. I have a macro script that was given to me by another member in this
: forum, I modified it, but it doesn't give me what I want. I need to
format
: every 3rd and fourth row with a different color. I found the correct
color.

So is the macro formatting the colors correctly or not? Do you want
different colors for each row?

: I also need this macro to reset the row formatting if a column gets added
or
: deleted.

If you run the macro again, it should update the row formatting. If you
want to automate this, see Jim Jackson's post

:My question is can I get a suggestion on what to do so that I can
: specify a range of cells versus what's in this script called
'ActiveSheet'.

Public Sub RowColor()
Dim i As Integer
Const RowStart As Integer = 1 'set this number to the starting row
Const RowEnd As Integer = 100 'set this number to the ending row

For i = RowStart To RowEnd
If (i - 1) Mod 4 1 Then
ActiveSheet.Rows(i).Interior.ColorIndex = 10
Else: ActiveSheet.Rows(i).Interior.ColorIndex = xlNone
End If
Next i

End Sub

This could also be written to prompt the user to enter the data, or it could
be written to use a selected range of cells. Please be more specific as to
what you want

: Also, can I associate this macro with a keyboard combination?

Go to Tools Macro Macros... select your macro from the list and click
the options... button.

Paul D

"PaulD" wrote:

"JeffH" wrote in message
...
: HI,
:
: I don't know much about Excel Macros but I'm trying to learn this as best
I
: can. I have a macro script that was given to me by another member in this
: forum, I modified it, but it doesn't give me what I want. I need to
format
: every 3rd and fourth row with a different color. I found the correct
color.

So is the macro formatting the colors correctly or not? Do you want
different colors for each row?

: I also need this macro to reset the row formatting if a column gets added
or
: deleted.

If you run the macro again, it should update the row formatting. If you
want to automate this, see Jim Jackson's post

:My question is can I get a suggestion on what to do so that I can
: specify a range of cells versus what's in this script called
'ActiveSheet'.

Public Sub RowColor()
Dim i As Integer
Const RowStart As Integer = 1 'set this number to the starting row
Const RowEnd As Integer = 100 'set this number to the ending row

For i = RowStart To RowEnd
If (i - 1) Mod 4 1 Then
ActiveSheet.Rows(i).Interior.ColorIndex = 10
Else: ActiveSheet.Rows(i).Interior.ColorIndex = xlNone
End If
Next i

End Sub

This could also be written to prompt the user to enter the data, or it could
be written to use a selected range of cells. Please be more specific as to
what you want

: Also, can I associate this macro with a keyboard combination?

Go to Tools Macro Macros... select your macro from the list and click
the options... button.

Paul D


Hi,

Thanks for your help. With your suggestions I have a working macro but I'd
like to clean it up a little. The new macro resets all lines except the
first line, by choice. The only problem is that I'd like to limit that to a
specific column, in this case 'H'. How can I do just that?

Public Sub RowColor()
Dim i As Integer
Const RowStart As Integer = 2 'set this number to the starting row
Const RowEnd As Integer = 200 'set this number to the ending row

For i = RowStart To RowEnd
If (i - 1) Mod 4 1 Then
ActiveSheet.Rows(i).Interior.ColorIndex = 35
Else: ActiveSheet.Rows(i).Interior.ColorIndex = xlNone
End If
Next i

End Sub

Thanks,
JeffH

"JeffH" wrote in message
...


: Thanks for your help. With your suggestions I have a working macro but
I'd
: like to clean it up a little. The new macro resets all lines except the
: first line, by choice. The only problem is that I'd like to limit that to
a
: specific column, in this case 'H'. How can I do just that?


Public Sub RowColor()
Dim i As Integer
Const RowStart As Integer = 2 'set this number to the starting row
Const RowEnd As Integer = 200 'set this number to the ending row

Range("H1").Interior.ColorIndex = 35 '<<--add this line
For i = RowStart To RowEnd
If (i - 1) Mod 4 1 Then
ActiveSheet.Rows(i).Interior.ColorIndex = 35
Else: ActiveSheet.Rows(i).Interior.ColorIndex = xlNone
End If
Next i

End Sub