Setting Interior Color Wipes Out Cell Borders

[TheAceMan1]

Howdy All ... Excel 2010

While troubleshooting a workbook I ran across an instance of a cells backcolor being changed from light red to white. During a break point I couldn't help but notice that the change removed the borders! ... I didn't notice it eariler because I format the borders sometime later in the code. The line of code that executes the change is:

Range("B2").Interior.Color = RGB(255, 255, 255)

Pretty straight forward stuff. Intrigued by this I checked sheets in other workbooks where I change the back color of cells (interior.color). Typically I'm just running an alternate backcolor scheme using conditional formatting. Setting a break point after the conditional formatting, reveals the borders are intact.

This is not a big deal and is easily corrected. I'd just really like to know why it happens.

Anyone have any idea/info why the borders are wiped out?

Note: you can see the effect by ececuting the line of code above on any cell (don't forget to change the cell reference) in a workbook.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Andrzejek]

I did try it, I made my borders on all 4 sides for cells A1 to C4, tried your code, it did not "removed the borders!".

I change some RGB colors to something other that white, the borders are still OK.

Are you sure that's the sigle line of code that does that?
Do you have any other code before or after this line that would mess with cell's borders?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

Probably you mean masking part of worksheet's grid by filling the interior. Fresh cell has no borders. As Andy pointed, if the cell has a border, it stays, whatever the interior is.

combo

 

[zelgar]

I think the problem is because the .Color option can be used to change the color of several things including the borders. (Note: Is it changing an established border or the "default" cell border that isn't printed? When I used your code, it only erased the "default" cell border and not any established border for the cell).

A way around this "border" issue is to use the .ColorIndex instead of the .Color so your code would look like this
Range("B2").Interior.ColorIndex = 0

That would set it to be white, you could also use an value of -4105 which sets it back to the automatic color or -4142 for no color.

 

[TheAceMan1]

To All ...

I should've been more clear but yes ... it only occurs with fresh cells that have no borders formatted. I suspect this is normal under these circumstances. Since I always put in borders (I did mention that I put them in after execution of the code in question) it makes the issue kind of academic. I just thought it very curious a thing I've not noticed before.

Thanks for your time Andrzejek, combo, zelgar. You guys take care ... Ya Hear!

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[zelgar]

Note, the .ColorIndex doesn't get rid of the worksheet's grid (which is what I think you were concerned about).

 

[combo]

ColorIndex is like number of tin of paint for a workbook, the colour palette can be defined by Colors collection. Applying either RGB or ColorIndex to Interior performs the same action, the only difference is that RGB sets the nearest colour in the palette, ColorIndex any colour assigned to this index. ColorIndex type is variant, for 0 it removes colour from the cell.

combo

 

[TheAceMan1]

Howdy All ...

The following are eye views of what happens:

Before:

After :

Note: this occurs with Interior.Color, Interior.ColorIndex, and Conditional Formatting!

To see this you have to select "white" as the interior (background) color ...

Cheers All! ...


See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[combo]

As in my first post: the grid on the first picture comes from workbook's window settings, it has nothing to do with cells borders. Filling cells interior masks this grid. Set cell's borders and they will stay preserved, unless you remove them in conditional formatting.

combo