Excel 2013 not highlighting formula range

[thisisrahul]

I have recently moved to Excel 2013. Apart from its Slow response, I am facing one more issue while writing any range formula.
When I am referring to a range with in the sheet it highlights the range but when I am referring to other workbook, it doesn't highlight anything. Only way to find out which range is in focus is by looking in Formula bar.

Is there some setting to enable it.

 

[Alex Blakenburg]

when I increase the number of workbook to 5-7 it started creating the issue.

That sounds more memory related.
The next time you manage to reproduce the issue can you have a look under Home > Cell Styles and tell me if you can see the section heading Good, Bad and Neutral with Normal under that very close to the top of the screen OR if you see many many repetitions of 20% - Accent 1 under the Custom section which would have pushed th other section down off the screen ?

 

[thisisrahul]

That sounds more memory related.
The next time you manage to reproduce the issue can you have a look under Home > Cell Styles and tell me if you can see the section heading Good, Bad and Neutral with Normal under that very close to the top of the screen OR if you see many many repetitions of 20% - Accent 1 under the Custom section which would have pushed th other section down off the screen ?

I can see few repetitions of "20% - Accent 1" and below that many other styles (around 150 styles) before the section "Good, Bad and Neutral".

 

[Alex Blakenburg]

Since I have seen it crack the 60k of custom styles that is not too bad and probably not enought to cause your memory issue.
If you do want to try on a copy of your workbook to run this macro and it will delete the custom styles. You, might have the issue in more than workbook though.
I can't remember if the problem was caused by 2010 or 2013, but it wasn't fixed until a later version than those 2.

Sub StyleKill()

' Deleting Unwanted Styles
    Dim styT As Style
    Dim intRet As Integer
    Dim intCnt As Long
    Dim totStylesCnt As Long
    Dim oldStatusBar As Boolean
    Dim i As Long
    Dim strMsg As String
        
    'store the status bar setting
    oldStatusBar = Application.DisplayStatusBar
    'display the status bar
    Application.DisplayStatusBar = True
 
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
    
    totStylesCnt = ActiveWorkbook.Styles.Count
    
    With ActiveWorkbook
        For i = totStylesCnt To 1 Step -1
            If Not .Styles(i).BuiltIn Then
                
                On Error Resume Next
                .Styles(i).Delete
                On Error GoTo 0
            
                intCnt = intCnt + 1
                
                If intCnt Mod 500 = 0 Then
                    'display a message
                    strMsg = "Total No of Styles " & totStylesCnt & " - Deleted " & intCnt & " Styles"
                    Application.StatusBar = strMsg
                End If
                
            End If
    
        Next i
    End With
    
    MsgBox "No of Styles Removed:- " & intCnt
    
    'remove any text in the status bar area
    Application.StatusBar = False
    'reset the status bar to the user's preference
    Application.DisplayStatusBar = oldStatusBar
    
End Sub

 

[thisisrahul]

thank you, but this helps only for the active workbook or where i've put this code. But whenever I open any new excel, same issue gets created.

 

[Alex Blakenburg]

I have that code in my personal macros workbook so that I can run it on the ActiveWorkbook. You can put it in any workbook, just make sure it is open when you want to run it and that the Activeworkbook is the one you want to clean up.
It has been quite a while since I had Office 2010/2013 and I seem to recollect that eventhough styles are workbook specific that it affected the whole Excel session.

The workbooks most likely to be affected are those that get resused each month and have things pasted in from multiple sources.

So you will need to open the workbooks you are using to test your issue one at a time and check the Styles panel after each one to see which workbook is causing the issue.
Once you have fixed it on the workbook save it and close Excel and then reopen it.

If you only have around 150 custom styles I can't guarantee it will fix your issue so if you don't want to bother that's fine.
If you do get to higher numbers though it will eventually at best wipe out all the formatting in the workbook and at worst make your workbook unrecoverable.