Macro hides rows on button press but other Functions referenced then display ERROR message

I want to be able to hide and unhide rows containing calculations etc using a macro accessed by a button. I have done this and the rows hide/unhide correctly. However cells in another worksheet, the hidden area and in the visible areas have functions which then return an #VALUE! error. If I hide the rows manually by selecting them and using right-click Hide, the functions continue to work normally.

The macro is simply:

sub
ActiveSheet.Rows("32:85").Hidden=True
end sub

and the function is:

Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

and also

=IF(AW39>=AW18,"OK","Needs Cover") (this is for the range of columns C:AX)

and also

a larger Function called DisplayedColour which returns a value based on the visible colour of a cell.

I don't understand why the macro should have such an impact on the rest of my spreadsheet. Worst case is I hide the rows manually but it would be nice to have the buttons.

Any ideas as to where it has all gone wrong??

Thanks,

dougielb

Hi JasperD,

That's perfect thank you - it does exactly what I was looking for. Just for my knowledge, I assume that the EnableEvents function/trigger(?) fires my other functions into working properly?

Many thanks again,

dougielb