How to pass a Define Named Range as a parameter to a Sub?

[inddon]

Hi There,

I have 3 defined named ranges in 3 different worksheets.

The following is the functionality:
1. Sub CheckMandatoryFields
It check if the 3 defined named ranges has a value.
2. Sub ChangeInvalidColour
If defined name range is empty then fill the interior to color yellow else no fill.

At the moment I am Selecting the named range and then calling the Sub ChangeInvalidColour. I am looking for a way to make this generic to pass the defined named range as a parameter to Sub ChangeInvalidColour

Could you advise, as to how this can be achieved?

Below sub's for your reference:

Sub CheckMandatoryFields()
EntWSDE_Header is defined as worksheet

   If EntWSDE_Header.[FVMMReferenceDate] = "" Then
      EntWSDE_Header.[FVMMReferenceDate].Select
      ChangeInvalidColour
   End If
End Sub


'Sub ChangeInvalidColour(r As Range)
Sub ChangeInvalidColour
  'With range name as parameter
       'Fill colour Yellow
       With Selection.Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 9699327
         .TintAndShade = 0
         .PatternTintAndShade = 0
       End With
  'End With
End Sub

Many thanks for your help.

Look forward to hearing from you.

Regards,
Don

 

[Chihiro]

Named ranges are generally referenced like...

Range("NamedRange")

Then to pass as parameter, you'd set your Sub ChangeInvalidColour to have argument for named range.

Ex:

Sub ChangeInvalidColour(rng As Range)

then you'd run it with argument.

ChangeInvalidColour(Range("NamedRange"))

 

[Debaser]

But either remove the parentheses around the argument, or use Call.