MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function to return interior color code

Posted by Kevin James on May 25, 2001 3:58 PM

Dagnabit! this can't be that hard.

Here is what I've tried:
Function CN(CNbr)
CNbr = Range(CNbr).Interior.ColorIndex
End Function

CNbr = Range(CNbr).Interior.ColorIndex.Value

either way, I get: #Value! as a result in the function-call cell:

A1 is interior colored as yellow.
In B1 I entered the function: =CN(A1)

Posted by Barrie Davidson on May 25, 2001 4:07 PM

Hi Kevin, try this
Function CN(CNbr As Range)
CN = CNbr.Interior.ColorIndex
End Function


Posted by Kevin James on May 25, 2001 4:41 PM

thanks. The learning curve is steep right now.


I'll sure be happy when all this finally makes crystal-clear sense and I get as proficient as you, Dave, Aladin and others.


Posted by Dave Hawley on May 25, 2001 5:29 PM

Re: thanks. The learning curve is steep right now.

Hi Kevin

While your still fresh with VBA (not set in your ways) try and get into the good habit of dimensioning Variables (Dim). For your particular function you would use:

Function CN(CNbr As Range) As Integer
CN = CNbr.Interior.ColorIndex
End Function

While this will not make any real noticable difference in a small project, it will and can it a larger one.

You may also want to consider using:

Function CN() As Integer

CN = ActiveCell.Interior.ColorIndex

End Function

This way you can use the Function without any arguments and have it return the Color Index of the cell it is used in.


OzGrid Business Applications

Posted by Barrie Davidson on May 27, 2001 12:10 PM

Thanks for the compliment but........

I'm afraid I'm not quite in the league of Dave and Aladin, but I'm hoping to get there!!