I run a macro which changes attributes of a cell and then cannot restore gridlines

[joe31623]

(...cannot see the gridlines -- they're there but somehow hidden).

I run this macro across my workbook, it changes cell properties, and then I am unable to see gridlines. When I use excel's ribbon in the program to turn off all gridlines and turn on all gridlines, there is no change. Here is what I mean by gridlines: https://support.office.com/en-us/art...a-b26f4adc3fb4

Every cell this code loops over results in a hidden gridline. MsgBox(C.Application.ActiveWindow.DisplayGridlines) displays "True" so there is no point to running code:
WS.Activate
ActiveWindow.DisplayGridlines = True

That leads me to believe there is an attribute of each Cell: C that is changed; but I don't know what attribute is changed.

I wish there was a way to list all attributes of a cell so I can more easily figure this out, but an Excel 2007 machine I have doesn't have a TypeLib.dll and neither does mine so I can't follow the suggestions of others who have gone down that path (if I'm understanding that correctly)

Here is my macro that unintentionally hides gridlines, despite the fact that MsgBox(C.Application.ActiveWindow.DisplayGridlines) is True, where C is ANY cell in the workbook -- but now that I think about it, perhaps that would always be True or that would always be False for any cell in the same worksheet... no matter, another lesson for another day.

Sub RemovingCFButNotEffects()
    'Removing Conditional Formats But Not The Effects
    '------------------------------------------------
    Dim Rng As Range, R As Range, C As Range
    Dim WS As Worksheet
    Dim LastRow As Long, LastColumn As Long, LastColumnOfRow As Long
    Dim C_LineStyle As Double, C_Weight As Double
    
    Application.ScreenUpdating = False
        
    
    For Each WS In ThisWorkbook.Worksheets
        Set Rng = WS.Range(WS.UsedRange.Address)
        LastRow = Rng.RowS.Count
        LastColumn = Rng.Columns.Count
        For Each R In Rng.RowS
            LastColumnOfRow = R.Cells(R.Row, LastColumn).End(xlToLeft).Column
            For Each C In R.Cells
                With C
                    If .Column <= LastColumnOfRow Then
                        .Interior.Color = .DisplayFormat.Interior.Color
                        .Font.FontStyle = .DisplayFormat.Font.FontStyle
                        .Font.Color = .DisplayFormat.Font.Color
                        .FormatConditions.Delete
                    Else
                        Exit For
                    End If
                End With
            Next C
        Next R
        WS.Activate
        ActiveWindow.DisplayGridlines = True
    Next WS
    Application.ScreenUpdating = True
    
    MsgBox ("The Conditional Formats In The Range " & Rng.Address & vbCrLf & "Has Been Removed But Not The Effects")
End Sub

If someone has the dll necessary to run the following code, I would appreciate seeing the results!

' PGC Jun 2009
' Lists the enumerators of a library
Sub ListConstants()
Dim oTLIApplication As TLI.TLIApplication
Dim oTLITypeLibInfo As TLI.TypeLibInfo
Dim oTypeInfo As TLI.TypeInfo
Dim oTLIMember As TLI.MemberInfo
Dim sLib As String
Dim lType As Long
Dim WS As Worksheet
 
sLib = "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.exe" 'for excel 365
'sLib = "c:\program files\microsoft office2007\office12\excel.exe" ' for excel 2007
'sLib = "c:\program files\microsoft office2k\office\excel9.olb"        ' for excel 2000

Set oTLIApplication = New TLI.TLIApplication
Set oTLITypeLibInfo = oTLIApplication.TypeLibInfoFromFile(sLib)
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
    For Each oTypeInfo In oTLITypeLibInfo.TypeInfos
        If oTypeInfo.TypeKind = TKIND_ENUM Then
            For Each oTLIMember In oTypeInfo.Members
                lType = lType + 1
                .Range("A" & lType) = oTypeInfo.Name        ' enumerator
                .Range("B" & lType) = oTLIMember.Name       ' constant
                .Range("C" & lType) = oTLIMember.Value      'value
            Next oTLIMember
        End If
    Next oTypeInfo
End With
End Sub

[gbeats101]

Hi

Not sure if this is the problem, and i have no idea what the dll is doing but ...

If .Column <= LastColumnOfRow Then
         .Interior.Color = .DisplayFormat.Interior.Color '<- what color is this returning
         .Font.FontStyle = .DisplayFormat.Font.FontStyle
         .Font.Color = .DisplayFormat.Font.Color
         .FormatConditions.Delete
Else

it maybe that the gridlines are hidden by the background color, also in the view pane on the ribbon can you check and uncheck the gridlines, have you also checked the color of the gridlines in the options, have you also tried setting the fill to 'None'?

Just throwing some ideas around, give them a try.

Thanks
Paul Smith

[joe31623]

Paul,

Good suggestion. When I ran that code with modification:

If .Column <= LastColumnOfRow Then
     MsgBox(.Interior.Color)
     MsgBox(.DisplayFormat.Interior.Color)
'/.
'/.
'/.

I got the same value: 16777215 (for cells with no interior color), in both Message Boxes, which leads me to believe it's not being changed. Just to verify I'm seeing that value change with color, I ran the code after manually setting the interior color of one cell to yellow and got 65535 for both values: .Interior.Color and .DisplayFormat.Interior.Color.

The purpose of the macro is to go through the workbook, set all colored cells that are colored based on conditional formatting to the color they are (regardless of conditional formatting) and then remove the conditional formatting.

[gbeats101]

if you set a color using

Interior.Color = 0 'or any number, it always gets filled and hence the gridlines wont be shown

Interior.Colorindex = xlNone 'which is -4142 which should remove any fill

Try this

Thanks
Paul S.

[joe31623]

THANKS, Paul!

That was exactly what I needed.

[gbeats101]

No Problem