Macro Removes Cell Borders

[Thomas M.]

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

 

[Don Guillett]

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

 

[Tom Ogilvy]

Clear clears all formatting. Try using ClearContents

Sub MarkClear()

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

End Sub

 

[Thomas M.]

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

--Tom

 

[Thomas M.]

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