Posts by maddog.

    Re: Use Cell Color In Formula Without VBA

    I did not mean to "hijack" (whatever that means to you). My intention was not to create a new thread since this question was so similar.
    If that was a problem, I am sorry. I will start new threads...

    I missed it also about [CODE] tags which I will do from now on. Thanks for the advice. I do not wish to ruffle feathers.


    Re: Use Cell Colour In Formula Without Vba

    Ahh, I see my flaw (though I would have like it if Microsoft in their infinite wisdom had informed me that I had an overflow!).

    My function should be returning a Long instead of an Integer

    Function CellColor(r As Integer, c As Integer) As Long
    CellColor = ActiveSheet.Cells(r, c).Interior.Color
    End Function

    So now this works. However in VBA the function Hex is not working though I may be able to figure that out, so I withdraw my question.
    Thanks anyway.


    Re: Use Cell Colour In Formula Without Vba

    I am activating this thread after much delay because I have a similar issue here.

    In my case I am using it as debugging method of my code. What I am doing is I have say column of cell that have various colored interiors.
    At 3 columns over, I am attempting to create a worksheet function to retrieve the color value of color cell for that row. In the previous
    column I was able to do this for the cells interior color index just fine. (Note: I just hate .offset() as I can not seem to keep indices straight)

    Below are the two functions I create in VBA:

    Function CellColorIndex(r As Integer, c As Integer) As Integer
    CellColorIndex = ActiveSheet.Cells(r, c).Interior.ColorIndex
    End Function

    Function CellColor(r As Integer, c As Integer) As Integer
    CellColor = ActiveSheet.Cells(r, c).Interior.Color
    End Function

    Ideally I would like the result in hex as CellColor = Hex(ActiveSheet.Cells(r, c).Interior.Color) though when I use CellColor() in a cell on the worksheet
    I get some form of referencing error "I am not catching". I am currently not catching anything.

    Instead of the bold line of code I have also tried the following (none of which works -- same error I suspect)

    1. CellColor = ActiveCell.Offset(r, c).Interior.Color

    2. ActiveCell.Offset(r, c).Activate
    CellColor = ActiveCell.Interior.Color

    3. ActiveCell.Offset(r, c).Select
    CellColor = Selection.Interior.Color

    Google Documentation shows these as working. When I put 1, 2 or 3 in the Immediate Window they retrieve the correct value.
    Hmm??? When I debug the worksheet function it errors out - popping me out of debug at the line of code with .Interior.Color value.

    So why is it that the .Interior.ColorIndex property is useable within VBA called as a WorksheetFunction yet .Interior.Color property
    is not???

    This inconsistency in Microsoft in use of VBA is what most offends me. Any ideas would be most appreciated.