cgmojoco
Well-known Member
- Joined
- Jan 15, 2005
- Messages
- 699
It appears that the intended formatting of this code (excerpt) does not work the same when used in conjunction with code that attempts to disable all screen updating (screen updating code posted further below):
have a toggle as follows:
For some reason, the first code works as intended if I do not toggle the screen updating off. When the toggling occurs the results of the formatting are different when the script is run as a whole. Specifically, several rows are formatted with respect to background and font color (rather than just the intended row 1)---and the freeze pane doesn't appear to freeze the first row but a subsequent row rather.
Any suggestions that point me in the right direction? (I suspect there will also be recommendations not to use the Select command either...alternative method welcomed).
Thank you in advance-
Chris
Code:
'Fill the cells in the first row that contain anything with dark blue back and white font.
Rows("1:1").AutoFilter
Cells.EntireColumn.AutoFit
Range("A1", Selection.End(xlToRight)).Select
With Selection
.Interior.ColorIndex = 55
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
End With
'Goto top left most corner of sheet and freeze panes so the first row is frozen.
Application.GoTo reference:=Range("A1"), Scroll:=True
Range("A2").Select
ActiveWindow.FreezePanes = True
Application.GoTo reference:=Range("A1"), Scroll:=True
have a toggle as follows:
Code:
Sub ToggleEvents(blnState As Boolean)
'*********************************************
'*********************************************
'Toggles Display updates in Excel
'Written by Zack Barresse, aka firefytr
'*********************************************
'*********************************************
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState = True Then
.StatusBar = False
End If
End With
End Sub
For some reason, the first code works as intended if I do not toggle the screen updating off. When the toggling occurs the results of the formatting are different when the script is run as a whole. Specifically, several rows are formatted with respect to background and font color (rather than just the intended row 1)---and the freeze pane doesn't appear to freeze the first row but a subsequent row rather.
Any suggestions that point me in the right direction? (I suspect there will also be recommendations not to use the Select command either...alternative method welcomed).
Thank you in advance-
Chris