Custom Function - passing an arg. as a worksheet name 1

[Vmanda]

Hello,
I am trying to write a custom function. The function takes two arguments, state & HazardGroup. Depending on which state is used, the range will be based on a different worksheet. I would like to be able to use the state arg to choose the worksheet, but I haven't been able to get it to work. Here is the code I am trying:

Dim FirstRange As Range

Set FirstRange = ActiveWorkbook.Sheets(State).Range("a7:a47&quot

Any thoughts on this?
thanks
Mandy

 

[ilses]

Hi Mandy,

If your sheets are named for the states, pass the name of the selected state to the function as a string:

Private Function ColorState(State As String)
Dim FirstRange As Range
Set FirstRange = ActiveWorkbook.Sheets(State).Range("a7:a47")
FirstRange.Interior.ColorIndex = 5
End Function

Sub test()
'get a state name as string from whatever input method you use
'pass it to the function
'workbook contains a sheet with the named Ohio
Call ColorState("Ohio")
End Sub

ilses

 

[Vmanda]

Thanks, that seems to be working.