multiple font.colorindex VBA code causing "Unresolved Content" error

[psolis]

I'm using the following VBA code in excel 2007 to change the font color & bold when a word occurs within a range (rng).

The range is column "L" over 618 rows. On its own it works great! it finds the word in each occurrence within range & changes the properties.

Sub Bold_Stone()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer
Dim Tag_str
Tag_str = "STONE"
Set rng = Range("L3:L618")
For Each Cell In rng
start_str = InStr(Cell.Value, Tag_str)
If start_str Then
Application.StatusBar = "Updating " & Tag_str & " Tags ..."
With Cell.Characters(start_str, Len(Tag_str)).Font
.Bold = True
.ColorIndex = 45
End With
End If
Next
End Sub

But over time I have created 70+ versions for different words & colorindex. when I run them all at once, I get an "Unresolved Content" error and all my data disappears.

I would love any ideas on how to :
1. fix why multiple subs are causing the "Unresolved Content" errors & deleting my data.
2. Consolidate it into a single sub for many word/colodindex combinations.

Any suggestions??

Thanks!
Pablo

 

[Andrew Poulsom]

Welcome to MrExcel.

What do you mean by "unresolved content" and "all my data disappears" exactly?

 

[psolis]

Andrew thank you for replying

I run all 70+ subs in a single Workbook_BeforeClose() routine & then save the file. Next time I open the file I get this error: "Excel found unreadable content in wtc_timeline.xlsm. Do you want to recover the content of the workbook?"

When I reply yes, it returns my spreadsheet with no data in all cells. This is what my file looks like after repairing:
psolis.com/images/excel_ucError2.jpg


And this is the message box that results from answering yes.
psolis.com/images/excel_ucError1.jpg

thanks
Pablo

 

[Andrew Poulsom]

From Googling that seems to be a fairly common error. Does it still happen if you don't run your BeforeClose procedure? Have you tried saving the workbook as xlsb or xls?

 

[psolis]

No it doesn't, which seems odd because BeforeClose is only to consolidate all macros into one operation. it doesn't do anything else.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' All Macro
' Run All
'
    If MsgBox("Do you want to update Tags before closing?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
' If answer is YES then do following Else go to End Sub
'

Application.Run "WTC_Timeline.xlsm!Sheet1.wordA"
... repeated for each word...
Application.Run "WTC_Timeline.xlsm!Sheet1.wordZ" 
 

'
'
' Clean up from Macro settings
Application.StatusBar = False

End Sub

I have a feeling that running many at once is causing some kind of glitch.

I will try saving as *.xls and see what happens.

Thanks
Pablo