AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
After applying a new Interior.Color, how do you keep (or restore) the small dotted borders around each cell ?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Applying an interior.color shouldn't affect the borders, post any code that you are using.
 
Upvote 0
Applying an interior.color shouldn't affect the borders, post any code that you are using.
I think the OP means "grid lines" (not "cell borders") as these get covered by cell fills and need to be replaced by actual cell borders (which appear above cell fills).
 
Upvote 0
I think the OP means "grid lines" (not "cell borders") as these get covered by cell fills and need to be replaced by actual cell borders (which appear above cell fills).

Maybe, if yes then still need to know the ranges and preferably (if there is any) the code applying the color :biggrin: even then will have to take a guess with the linestyle as my gridlines are solid not dotted.
 
Last edited:
Upvote 0
Maybe, if yes then still need to know the ranges and preferably (if there is any) the code applying the color :biggrin: even then will have to take a guess with the linestyle as my gridlines are solid not dotted.
If I look with a magnifying glass, I see mine are solid too however, to the naked eye, they look finely dotted.
 
Upvote 0
Dang Rick you must have a good naked eye.....I zoomed to 400% and still look solid to me...:LOL:
 
Upvote 0
Hi,

Yes it is grid lines... default ones that are there if you Insert a new Worksheet.

I do have some code
Code:
 With Worksheets("Sheet1").Range("Y:CP")
        .ColumnWidth = 3
        .Interior.Color = RGB(192, 192, 192)
        .Font.Color = vbBlack
        .Font.Name = "Arial Narrow"
        .Font.Size = 10
   End With

But you get the same effect with r-click - Format - Fill
 
Upvote 0
This then

Code:
Sub MM1()
With Worksheets("Sheet1").Range("Y:CP")
        .ColumnWidth = 3
        .Interior.Color = RGB(192, 192, 192)
        .Font.Color = vbBlack
        .Font.Name = "Arial Narrow"
        .Font.Size = 10
   End With
With Worksheets("Sheet1").Range("Y:CP").Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
End Sub
 
Upvote 0
Thanks Michael, it's getting closer. Color black is too dark as is RGB(192,192,192) I can't find any default color.
But vertical lines aren't wanted only horizontal. I can see whats needed in Format -Fill
so playing around with that. Not quite there yet.
 
Upvote 0
The color is RGB(218,220,221). r-Click-Format-Border (not Fill) wasn't quite right as I was trying to remove the default vertical lines.
I had to add a third With Clause as couldn't figure out how to include everything in one. (Presume I ave to Set Linestyle before I can remove it)

Code:
    With Worksheets("Sheet1").Range("Y:CP")
        .ColumnWidth = 3
        .Interior.Color = RGB(192, 192, 192)  'Grey
        .Font.Color = vbBlack
        .Font.Name = "Arial Narrow"
        .Font.Size = 10
    End With
    With Worksheets("Sheet1").Range("Y:CP").Borders
        .LineStyle = xlContinuous
        .Color = RGB(218, 220, 221)
        .Weight = xlThin
        End With
    With Worksheets("Sheet1").Range("Y:CP")
        .Select
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
    End With
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top