Fill color getting removed in vba???

[andrewb90]

Hello all,

I have this code that I am running that clears the contents and text/fill colors of certain cells in my sheet. The problem that I am facing is that a few merged cells are having the fill color removed and I can't figure out why.
They are:
D88:V88, D107:V107, D126:V126, D145:V145, and D164:V164.

Here's the code:

Private Sub Reset()Dim msg, Style, Title
msg = "Are you sure you want to delete the schedule?" & vbNewLine & "Employee information will not be changed"
Style = vbYesNo + vbExclamation
Title = "Just to make sure...."
Response = MsgBox(msg, Style, Title, Help, Ctxt)
If Response = vbNo Then
    MyString = "No"
    Exit Sub
Else
    MyString = "Yes"
    Sheets("Print").Visible = True
    Sheets("Print").Select






Sheets("Scheduler").Select
Range("H84:J84").ClearContents ' the date


Range("E89:R106").ClearContents ' front of house
Range("E89:R106").Select
Call Clear_reset
Range("B89:B106").ClearContents


Range("E108:R125").ClearContents
Range("E108:R125").Select
Call Clear_reset
Range("B108:B125").ClearContents




Range("E127:R144").ClearContents ' back of house
Range("E127:R144").Select
Call Clear_reset
Range("B127:B144").ClearContents


Range("E146:R163").ClearContents
Range("E146:R163").Select
Call Clear_reset
Range("B146:B163").ClearContents




Range("E165:R192").ClearContents ' prep
Range("E65:R192").Select
Call Clear_reset
Range("B165:B192").ClearContents




Range("E194:R207").ClearContents ' mgr
Range("E194:R207").Select
Call Clear_reset
Range("B194:B207").ClearContents


Range("E85:R85").ClearContents ' projections
    
Range("N2").Select ' resetting individual email function
ActiveCell.FormulaR1C1 = "0"


Call Reset_MasterAvail


End If
Sheets("Print").Visible = False
Range("A1").Select
ActiveWorkbook.Save
MsgBox "The sheet has been reset and is now ready for use."
End Sub

And here's the code that gets called:

Sub Clear_reset() Dim rngMyRow As Range
Sheets("Print").Visible = True
With Selection
    .Interior.Color = xlNone
    .Font.ColorIndex = xlAutomatic
End With


Sheets("RO").Visible = True
Sheets("RO").Range(Selection.Address).Offset(-85, -2).ClearContents
Sheets("Scheduler").Select
Columns("C:C").Hidden = False
Columns("B:B").Hidden = True
End Sub

Any help would be greatly appreciated!!!

 

[RoryA]

I suggest you step through the code and check the ranges being selected at each stage. I suspect the problem is there. Also it is unnecessary to select ranges to manipulate them - you could use:

Range("E165:R192").ClearContents ' prep
Call Clear_reset(Range("E65:R192"))
Range("B165:B192").ClearContents

Range("E194:R207").ClearContents ' mgr
Call Clear_reset(Range("E194:R207"))
Range("B194:B207").ClearContents


Range("E85:R85").ClearContents ' projections
    
Range("N2").Value2 = 0

Call Reset_MasterAvail


End If
Sheets("Print").Visible = False
Range("A1").Select
ActiveWorkbook.Save
MsgBox "The sheet has been reset and is now ready for use."
End Sub

Sub Clear_reset(rgClear as Range)
Sheets("Print").Visible = True
With rgClear
    .Interior.Color = xlNone
    .Font.ColorIndex = xlAutomatic
End With

Sheets("RO").Visible = True
Sheets("RO").Range(rgClear.Address).Offset(-85, -2).ClearContents
Sheets("Scheduler").Select
Columns("C:C").Hidden = False
Columns("B:B").Hidden = True
End Sub