Preserve the original gridlines after fill color?

[eliW]

Hi all,

How can fill color range of cells but still preserve the original gridlines?

Eli

 

[Colo]

Hi all,

How can fill color range of cells but still preserve the original gridlines?

Eli

Hi eliW !

Please give it a try!

http://www.interq.or.jp/sun/puremis/colo/code/014.htm

 

[eliW]

Hi eliW !

Please give it a try!

http://www.interq.or.jp/sun/puremis/colo/code/014.htm

Thank you Colo.

I checked your link but sorry I don't know how to activate it!

Can you give some instructions how to copy the codes into the modules?

Much appreciation,

Eli

 

[Colo]

OK,

Place this code into your standard module.

'// Std Module
'// Run ChangeEventOn, The format is prevented.
Option Explicit
Public ChangeEventFlg As Boolean
Sub ChangeEventOn()
    ChangeEventFlg = True
End Sub
Sub ChangeEventOff()
    ChangeEventFlg = False
End Sub

'// ThisWorkbook module

Place this code into your ThisWorkbook module.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo ErrLine
 If ChangeEventFlg = False Then Exit Sub
    Dim Formerdata As Variant, ChangedData As Variant
    With Application
     .EnableEvents = False
    .Undo
    Formerdata = Target.Formula
    .Undo
     ChangedData = Target.Formula
    .Undo
    Target.Formula = ChangedData
ErrLine:
     .EnableEvents = True
    End With
End Sub

eli if you don't get it in regard with where the code should be placed, please have a look our MVP dk's site.
http://www.danielklann.com/Excel/where_do_i_place_code_in_excel.htm

:D

Oh I forgot to say how to activate it.... all you have to do is run ChangeEventOn from Tools>Macro>Macros.
If you would like to turn it off then run ChangeEventOff.

 

[eliW]

Hi Colo,
First of all thank you so much.
Now I did exactly what you've instructed but nothing happens!

Im using Excel 97 Win 98

What I expect is that if I fill a range of cells with any color - I want to see the gridline borders which now became invisible.

I can't figure it out how the Workbook code is doing this.

I would appreciate more help on this issue,

Regards,

Eli

 

[Colo]

Sorry Eli, I think I misunderstood your question.
This is trickey way but give it a try again pls.
Please place this code in ThisWorkbook module.

Dim rngPrev As Range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not rngPrev Is Nothing Then DrawBorders rngPrev
    Set rngPrev = Target
End Sub
Private Sub DrawBorders(ByVal Target As Range)
    Const lngColor As Long = 15    '25% Gary
    Dim lngCnt As Long
    Dim rng As Range    
    Application.ScreenUpdating = False    
    For Each rng In Target
    If rng.Interior.ColorIndex = xlNone Then
        With rng.Borders
            For lngCnt = 8 To 11
                If .ColorIndex = lngColor Then
                    .LineStyle = xlNone
                End If
            Next
        End With
    Else
        For lngCnt = 8 To 11
            With rng.Borders
                If .LineStyle = xlNone Then
                    .Weight = xlThin
                    .ColorIndex = lngColor
                End If
            End With
        Next
    End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[Colo]

I modified a little, so please use this one.

Option Explicit
Dim rngPrev As Range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not rngPrev Is Nothing Then DrawBorders rngPrev
    Set rngPrev = Target
End Sub
Private Sub DrawBorders(ByVal Target As Range)
    Const lngColor As Long = 15    '25% Gary
    Dim lngCnt As Long
    Dim rng As Range
    
    If Target.Rows.Count = Rows.Count Or Target.Columns.Count = Columns.Count Then
        MsgBox "Target range is too huge, so this event would not be executed."
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For Each rng In Target
    If rng.Interior.ColorIndex = xlNone Then
        With rng.Borders
            For lngCnt = 8 To 11
                If .ColorIndex = lngColor Then
                    .LineStyle = xlNone
                End If
            Next
        End With
    Else
        For lngCnt = 8 To 11
            With rng.Borders
                If .LineStyle = xlNone Then
                    .Weight = xlThin
                    .ColorIndex = lngColor
                End If
            End With
        Next
    End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[indiantrix]

Howabout a non-code solution? When formatting the cells on the pattern tab to the color desired, just click on the border tab and choose color=gray, then click both the outline and inside boxes. This is a workaround, but the visual effect is correct. What this does is places borders around the cells, so they are printed regardless of the File/Page Setup/Sheet/Print Gridlines checkbox setting. Code is probably better solution, but this can avoid lots of aggravation. /s/ Larry

 

[eliW]

Thank yoy again Colo, -I'll give it a try in the morning.

Indiatrix, I know of course how to do it manually, but i need to do it automatically within a code- thank you

Eli