Your second and third subroutines have the same name. You can't do that.
You will need to combine the code  from these two into one subroutine.

Is there anyway of specifing the hide  cells routine in an easier
argument instead of each row being named?

Private Sub ToggleButton1_Click()

With ToggleButton1
Rows(6).Hidden = .Value
Rows(8).Hidden = .Value
Rows(10).Hidden = .Value
Rows(12).Hidden = .Value
Rows(14).Hidden = .Value
Rows(16).Hidden = .Value
Rows(18).Hidden = .Value
Rows(20).Hidden = .Value
Rows(22).Hidden = .Value
Rows(24).Hidden = .Value
Rows(26).Hidden = .Value
Rows(28).Hidden = .Value
Rows(30).Hidden = .Value
Rows(32).Hidden = .Value
Rows(34).Hidden = .Value
Rows(36).Hidden = .Value
End With
End Sub

2 nd Procedure

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Formatting all input  as caps
Dim rngCell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range("B1:AQ37")) Is Nothing Then

For Each rngCell In Target
rngCell.Value = UCase(rngCell.Value)
Next rngCell

ElseIf Not Intersect(Intersect(Target, _
Range("I7:M37,Q7:T37,W7:AA37,AD7:AH37,AK7:AK37"))) Is Nothing
Then
For Each cl In Target
Select Case cl.Text
Case "": cl.Interior.ColorIndex = 0
Case "V": cl.Interior.ColorIndex = 44
Case "M": cl.Interior.ColorIndex = 43
Case "C": cl.Interior.ColorIndex = 6
Case "T": cl.Interior.ColorIndex = 32
Case "TB": cl.Interior.ColorIndex = 33
Case "H": cl.Interior.ColorIndex = 4
Case "HD": cl.Interior.ColorIndex = 4
Case "S": cl.Interior.ColorIndex = 3
Case "B": cl.Interior.ColorIndex = 16
End Select
Next cl
End If

ws_exit:
Application.EnableEvents = True
End Sub

Could you maybe explain your version of the code  please?

1st procedure  works fine but i was wondering if the hidden rows  could
be held in one argument ie(2,4,6,8,10)ect

2nd Procedure doesn't work  so could you explain what you've changed and
why.

Private Sub ToggleButton1_Click()

With ToggleButton1
Range("A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26,
A28, A30, A32, A34, A36").EntireRow.Hidden = .Value
End With
End Sub


In what way doesn't the second work?

Is there a way of Protecting the rows  that i hide  and then unprotect
them when i unhide them?

When these rows are hidden  it's possible to delete the data in them if
i select the rows around them and press delete even if they are
hidden.The users of my spreadsheet might delete these by mistake if
they are hidden.

Any ideas how i can do this?

To protect and hide  rows, you could try:

Cells.Locked = False
Rows("RowNum:RowNum").Locked = True
Rows("RowNum:RowNum").Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True

This would keep the row from being deleted, but the user would get the
"... is protected and is therfore read-only." Excel error  message when
the data is mistakenly deleted. I don't know how to avoid this error
message without what would probably be a great deal of work.

e.g.

for rownr = 6 to 36 step 2

Rows(rownr).EntireRow.Hidden = False

next rownr

There is no automated link between protection and hiding.

I tend not to hide  rows in the main part of a worksheet. I put information
I don't want cluttering up the place onto a different worksheet, or way off
to the right or way down at the bottom.

There's nothing to stop you protecting the information you're going to hide,
though. You just need to do it separately.

The cells  i hide  have to be where they are as they represent the value
of the holiday taken. I hide these rows  with a toggle button  so that i
can have a better overwiew of staff who are on holiday.

If someone was to delete some info by highlighting more than one row
then the hidden  cell values get deleted as well.

So i need to protect these cells when they are hidden and unprotect
when they are not.

I can do one or the other but i can't do both procedures using the same
button.

If i send you the spreadsheet would that help  you see what i'm trying
to do?

Quick question regarding the "extra" information.
In Excel if the cell has data in it, it will try to print the cells.
How do you get around this? Do you set your page area? What about
changes? Could the information be put on an extra worksheet  and then
use links then protect the linked cells  and the worksheet with the
original data?
I find that my users forget to reset the print area  then wonder why all
their information is not there.

Unless you are changing them on a regular basis, the cells  with the hours in
them don't need to be anywhere near the "H" cells or the formulas - they can
just as easily be on a different sheet.

In most instances, these hour values would be common either from day to day
or from week to week, and probably even from person to person. This would
mean that you don't need an hours value for every day anyway - you could
have all the formulas referring to a few common values.

However, I don't know your specific circumstances. If you want to keep this
information close to the other information, then that's fine, and you just
need to protect the cells at the same time as you hide  the rows.

What i'd like to do is specify a range  of cells  to be protected when hidden  ie
I6:M6,P6:T6,W6:AA6,AD6:AH6
and the same cells to be unprotected when unhidden.All controlled by a toggle
switch.

I also need the same on alternate rows  ie I8,I10,I12 ect

Any guidance on this very much appreicated.

Here is the code  i'm using.



Private Sub ToggleButton1_Click()

With ToggleButton1

Range("A6,A8,A10,A12,A14,A16,A18,A20,A22,A24,A26,A28,A30,A32,A34,A36").EntireRow\
.Hidden = .Value

End With
End Sub


Private Sub Worksheet_Change(ByVal target  As Range)

' Character Input as Caps

Static booBusy As Boolean
If booBusy = True Then Exit Sub
booBusy = True
Dim rngCell As Range, rngIntersect As Range
Set rngIntersect = Application.Intersect(Target, Range("B1:AQ37"))
On Error Resume Next
For Each rngCell In rngIntersect
rngCell.Value = UCase(rngCell.Value)
Next rngCell
On Error GoTo 0
booBusy = False

'Conditional Formatting

Dim rng As Range

Set rng = Intersect(Target,
Range("I7:M37,Q7:T37,W7:AA37,AD7:AH37,AK7:AK37"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case ""
cl.Interior.ColorIndex = 0
Case "V"
cl.Interior.ColorIndex = 42
Case "M"
cl.Interior.ColorIndex = 45
Case "C"
cl.Interior.ColorIndex = 7
Case "T"
cl.Interior.ColorIndex = 14
Case "TB"
cl.Interior.ColorIndex = 5
Case "H"
cl.Interior.ColorIndex = 4
Case "HD"
cl.Interior.ColorIndex = 4
Case "S"
cl.Interior.ColorIndex = 3
Case "B"
cl.Interior.ColorIndex = 16
Case "MD"
cl.Interior.ColorIndex = 43
Case Else
Exit Sub
End Select
Next cl
End If

End Sub

You are correct. If the extra information is on the same sheet, you will
need a print area.

If this is a problem, then an extra worksheet  is often a good way to go.

However, a workbook with this level of complexity is probably best not given
to users in a mode where they can add  rows or columns. They'll break it in
more ways than just not printing the extra stuff.