I am trying to 'Highlight' various single row ranges to indicate shift patterns across a worksheet. Columns are headed as days of the week.
My code essentially colours in a series of cells and indicates the hours to be worked each day.
Sub Highlight5daysE()
Dim MyRange As Range
Application.ScreenUpdating = False
MyCell = ActiveCell.Address
Range(MyCell).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 4).Select '5Days on Early Shift
Dim c As Range
For Each c In Selection
c.Value = 7.5
Next c
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Worksheets(1).Calculate
Range(MyCell).Select
Application.ScreenUpdating = True
End Sub
All this works fine until I try to protect the rest of the sheet (which holds all sorts of information); when I apply protection, I get an error (large Red cross) with the number 400 next to it. I am at a loss as to why this occurs and would appreciate any help anyone can give me regarding this.
I am colouring the cells using various colours to represent differing shift patterns and then using a UDF to count the various coloured cells so that I can see how many people are rostered for each particular shift.
If I can't protect the rest of the sheet, it is all too easy for anyone using the spreadsheet to 'wipe-out' many of the analysis areas of the sheet.
Can anyone help me with this problem at all? Your help would really be appreciated.
Domigos
My code essentially colours in a series of cells and indicates the hours to be worked each day.
Sub Highlight5daysE()
Dim MyRange As Range
Application.ScreenUpdating = False
MyCell = ActiveCell.Address
Range(MyCell).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 4).Select '5Days on Early Shift
Dim c As Range
For Each c In Selection
c.Value = 7.5
Next c
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Worksheets(1).Calculate
Range(MyCell).Select
Application.ScreenUpdating = True
End Sub
All this works fine until I try to protect the rest of the sheet (which holds all sorts of information); when I apply protection, I get an error (large Red cross) with the number 400 next to it. I am at a loss as to why this occurs and would appreciate any help anyone can give me regarding this.
I am colouring the cells using various colours to represent differing shift patterns and then using a UDF to count the various coloured cells so that I can see how many people are rostered for each particular shift.
If I can't protect the rest of the sheet, it is all too easy for anyone using the spreadsheet to 'wipe-out' many of the analysis areas of the sheet.
Can anyone help me with this problem at all? Your help would really be appreciated.
Domigos