Posts by maddog.

  • Use Cell Color In Formula Without VBA

    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.


    maddog.

  • Use Cell Color In Formula Without VBA

    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.


    maddog.

  • Use Cell Color In Formula Without VBA

    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.


    maddog.