VBA with a named range across multiple sheets

[jlg]

Hi all-
Got what I hope is a fairly simple vba question. I am working with a 7 sheet workbook, and each sheet includes (among other things) about 10 input cells. I have used the name manager to define the entire range of those input cells (across all sheets) as "input".

I was hoping to write a simple macro that simply would toggle the background color of these cells between blank and grey, but when I enter into vba, it gives me Run Time Error 1004: Range of Object Global Failed. Here's what I wrote for the macro to change all the cells in the input range to blank.

Sub InputBlank()
'
' InputBlank Macro
' changes input cells to no background
'
Range("input").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

I'm wondering if I have to specifically instruct the process to select each worksheet, or if vba simply doesn't work with named ranges across multiple sheets. Any help is appreciated. Thanks!

 

[dave3009]

Why do you toggle the background colour? Would it maybe be easier just to use conditional formatting?

 

[mikerickson]

AFAIK, a named range has to be all on one sheet. I've even more certain that a VBA range variable can only have cells from one sheet.

 

[jlg]

Thanks all. In that case it sounds like my best option may be to use separate ranges for each and activate each of them individually.

BTW, to dave's question, the goal of toggling the background color is for other users to easily identify which cells are input cells and which cells are locked. The goal is to have all input cells grey, but be able to turn the grey off when printing so that they will be easier to read in hard copy.

 

[SamTYler]

Define Name "Input" and set its RefersTo "=!$A$1:$B$7"

Sheets.Range("Input") will refer to Range A1:B7 on every Sheet

 

[dave3009]

With CF highlight each cell and use Formula Is =ISBLANK(cell) and set a grey format. Now when the cells are empty they are grey and when filled they will have the format defined in the sheet.

 

[jlg]

Perfect. Thanks to both of you!