Q: UDF with Interior.colorindex Help needed

M

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
 
J

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
 
J

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
 
M

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
 
C

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...
 
D

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
 
C

Charlotte E

Thanks :)


Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top