Q: UDF with Interior.colorindex Help needed

[MAXX]

Hi
I wrote a short function "interior"

Function interior(Rng) As Integer
Application.Volatile True
interior = Rng.interior.ColorIndex
End Function

And that works if I call a function with for instance =interior(c3) an i got
color index of cell c3
but I need color index of cell that contains the function ,
something like if in C3 I put =row() I will got 3

How to do this?

Thank you for reading this especially if you are able to help me out on
this.
Maxx

 

[Javed]

Function MyInterior(Optional Rng As Range) As Integer

Application.Volatile

If Rng Is Nothing Then
'This line must be in single line
MyInterior = Application.ThisCell.Interior.ColorIndex
Else
'This line must be in single line
MyInterior = Rng.Interior.ColorIndex
End If

End Function

Pls confirm

 

[Javed]

Function MyInterior(Optional Rng As Range) As Integer

Application.Volatile

If Rng Is Nothing Then
MyInterior = Application.ThisCell.interior.ColorIndex
Else
MyInterior = Rng.interior.ColorIndex
End If

End Function

 

[MAXX]

This is exactly what I was looking for
Thanks a lot!!!

"Javed" wrote in message

Function MyInterior(Optional Rng As Range) As Integer

Application.Volatile

If Rng Is Nothing Then
MyInterior = Application.ThisCell.interior.ColorIndex
Else
MyInterior = Rng.interior.ColorIndex
End If

End Function

 

[Charlotte E]

I could use this too

But, is it possible to 'expand' the function a little?

If you given a range of cells, i.e. =MyInterior(H3:J11), could the function
be made so it take the color of the first cell in the range?


TIA...

 

[Dave Peterson]

Option Explicit
Function MyInterior(Optional Rng As Range) As Long

Application.Volatile

If Rng Is Nothing Then
MyInterior = Application.Caller.Interior.ColorIndex
Else
MyInterior = Rng.Cells(1).Interior.ColorIndex
End If

End Function

 

[Charlotte E]

Thanks

Option Explicit
Function MyInterior(Optional Rng As Range) As Long

Application.Volatile

If Rng Is Nothing Then
MyInterior = Application.Caller.Interior.ColorIndex
Else
MyInterior = Rng.Cells(1).Interior.ColorIndex
End If

End Function