Excel 2003

This is a pretty trivial issue, but it's one of those things that is just
driving me nuts. I have the following very simple macros:

**********
Dim ActionCell As String
Dim ColorCodeCell As String
Dim ColorNumber As Integer

Private Sub BuildRangeVariables()

Dim RowNumber As Integer

RowNumber = ActiveCell.Row
ActionCell = "I" & RowNumber
ColorCodeCell = "H" & RowNumber

End Sub

Private Sub MarkRow()

BuildRangeVariables
Rows(ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = ColorNumber
.Pattern = xlSolid
End With

End Sub

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).Select
ActiveCell.Clear
Range(ActionCell).Select
ActiveCell.Clear

End Sub
**********

Basically, I have a spreadsheet with a few thousand rows, and I use a series
of simple macros to mark each row by using a fill color and adding a text
values to columns H and I. The macros that add the text values are not
shown here, but the value in column H is the name of a color (so that I can
use the filter to see all the rows marked with the given color) and the
values in column I can be "Remove," "Test," or "Update" (again, so I can
filter on those values). When I have completed the action for the given row
I run the MarkClear macro, which simply removes the fill color and clears
the values in columns H and I.

While I have omitted some macros, the routines shown above are the COMPLETE
logic flow for the MarkClear routine.

It all works just fine, but for one tiny irritating flaw. When I run the
MarkClear macro it also removes the cell borders. Can anyone tell me why
that happens?

--Tom

try xlnone instead of 0


Private Sub MarkRow()
Rows(ActiveCell.Row).Interior.ColorIndex = xlnone
End Sub

Sub MarkClear()
MarkRow
Range(ColorCodeCell).Clear
Range(ActionCell).Clear
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2003

This is a pretty trivial issue, but it's one of those things that is just
driving me nuts. I have the following very simple macros:

**********
Dim ActionCell As String
Dim ColorCodeCell As String
Dim ColorNumber As Integer

Private Sub BuildRangeVariables()

Dim RowNumber As Integer

RowNumber = ActiveCell.Row
ActionCell = "I" & RowNumber
ColorCodeCell = "H" & RowNumber

End Sub

Private Sub MarkRow()

BuildRangeVariables
Rows(ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = ColorNumber
.Pattern = xlSolid
End With

End Sub

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).Select
ActiveCell.Clear
Range(ActionCell).Select
ActiveCell.Clear

End Sub
**********

Basically, I have a spreadsheet with a few thousand rows, and I use a
series of simple macros to mark each row by using a fill color and adding
a text values to columns H and I. The macros that add the text values are
not shown here, but the value in column H is the name of a color (so that
I can use the filter to see all the rows marked with the given color) and
the values in column I can be "Remove," "Test," or "Update" (again, so I
can filter on those values). When I have completed the action for the
given row I run the MarkClear macro, which simply removes the fill color
and clears the values in columns H and I.

While I have omitted some macros, the routines shown above are the
COMPLETE logic flow for the MarkClear routine.

It all works just fine, but for one tiny irritating flaw. When I run the
MarkClear macro it also removes the cell borders. Can anyone tell me why
that happens?

--Tom

Clear clears all formatting. Try using ClearContents

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).ClearContents
Range(ActionCell).ClearContents

End Sub

--
Regards,
Tom Ogilvy


"Thomas M." wrote:

Excel 2003

This is a pretty trivial issue, but it's one of those things that is just
driving me nuts. I have the following very simple macros:

**********
Dim ActionCell As String
Dim ColorCodeCell As String
Dim ColorNumber As Integer

Private Sub BuildRangeVariables()

Dim RowNumber As Integer

RowNumber = ActiveCell.Row
ActionCell = "I" & RowNumber
ColorCodeCell = "H" & RowNumber

End Sub

Private Sub MarkRow()

BuildRangeVariables
Rows(ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = ColorNumber
.Pattern = xlSolid
End With

End Sub

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).Select
ActiveCell.Clear
Range(ActionCell).Select
ActiveCell.Clear

End Sub
**********

Basically, I have a spreadsheet with a few thousand rows, and I use a series
of simple macros to mark each row by using a fill color and adding a text
values to columns H and I. The macros that add the text values are not
shown here, but the value in column H is the name of a color (so that I can
use the filter to see all the rows marked with the given color) and the
values in column I can be "Remove," "Test," or "Update" (again, so I can
filter on those values). When I have completed the action for the given row
I run the MarkClear macro, which simply removes the fill color and clears
the values in columns H and I.

While I have omitted some macros, the routines shown above are the COMPLETE
logic flow for the MarkClear routine.

It all works just fine, but for one tiny irritating flaw. When I run the
MarkClear macro it also removes the cell borders. Can anyone tell me why
that happens?

--Tom

Thanks for the information. Unfortunately, I was unable to get this
approach to work.

--Tom

"Don Guillett" wrote in message
...

try xlnone instead of 0


Private Sub MarkRow()
Rows(ActiveCell.Row).Interior.ColorIndex = xlnone
End Sub

Sub MarkClear()
MarkRow
Range(ColorCodeCell).Clear
Range(ActionCell).Clear
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2003

This is a pretty trivial issue, but it's one of those things that is just
driving me nuts. I have the following very simple macros:

**********
Dim ActionCell As String
Dim ColorCodeCell As String
Dim ColorNumber As Integer

Private Sub BuildRangeVariables()

Dim RowNumber As Integer

RowNumber = ActiveCell.Row
ActionCell = "I" & RowNumber
ColorCodeCell = "H" & RowNumber

End Sub

Private Sub MarkRow()

BuildRangeVariables
Rows(ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = ColorNumber
.Pattern = xlSolid
End With

End Sub

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).Select
ActiveCell.Clear
Range(ActionCell).Select
ActiveCell.Clear

End Sub
**********

Basically, I have a spreadsheet with a few thousand rows, and I use a
series of simple macros to mark each row by using a fill color and adding
a text values to columns H and I. The macros that add the text values
are not shown here, but the value in column H is the name of a color (so
that I can use the filter to see all the rows marked with the given
color) and the values in column I can be "Remove," "Test," or "Update"
(again, so I can filter on those values). When I have completed the
action for the given row I run the MarkClear macro, which simply removes
the fill color and clears the values in columns H and I.

While I have omitted some macros, the routines shown above are the
COMPLETE logic flow for the MarkClear routine.

It all works just fine, but for one tiny irritating flaw. When I run the
MarkClear macro it also removes the cell borders. Can anyone tell me why
that happens?

--Tom

I looked up ClearContents in the Help and it sounds like it should cure my
problem, but in fact I have been unable to get it to work. The issue is not
very important, so at this point I think I'll just live with it.

--Tom

"Tom Ogilvy" wrote in message
...
Clear clears all formatting. Try using ClearContents

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).ClearContents
Range(ActionCell).ClearContents

End Sub

--
Regards,
Tom Ogilvy


"Thomas M." wrote:

Excel 2003

This is a pretty trivial issue, but it's one of those things that is just
driving me nuts. I have the following very simple macros:

**********
Dim ActionCell As String
Dim ColorCodeCell As String
Dim ColorNumber As Integer

Private Sub BuildRangeVariables()

Dim RowNumber As Integer

RowNumber = ActiveCell.Row
ActionCell = "I" & RowNumber
ColorCodeCell = "H" & RowNumber

End Sub

Private Sub MarkRow()

BuildRangeVariables
Rows(ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = ColorNumber
.Pattern = xlSolid
End With

End Sub

Sub MarkClear()

ColorNumber = 0
MarkRow
Range(ColorCodeCell).Select
ActiveCell.Clear
Range(ActionCell).Select
ActiveCell.Clear

End Sub
**********

Basically, I have a spreadsheet with a few thousand rows, and I use a
series
of simple macros to mark each row by using a fill color and adding a text
values to columns H and I. The macros that add the text values are not
shown here, but the value in column H is the name of a color (so that I
can
use the filter to see all the rows marked with the given color) and the
values in column I can be "Remove," "Test," or "Update" (again, so I can
filter on those values). When I have completed the action for the given
row
I run the MarkClear macro, which simply removes the fill color and clears
the values in columns H and I.

While I have omitted some macros, the routines shown above are the
COMPLETE
logic flow for the MarkClear routine.

It all works just fine, but for one tiny irritating flaw. When I run the
MarkClear macro it also removes the cell borders. Can anyone tell me why
that happens?

--Tom