+ Reply to Thread
Results 1 to 7 of 7

Cell Referring to Itself

Hybrid View

Crisi88 Cell Referring to Itself 06-04-2010, 11:42 AM
romperstomper Re: Cell Referring to Itself 06-04-2010, 11:51 AM
Crisi88 Re: Cell Referring to Itself 06-04-2010, 01:19 PM
romperstomper Re: Cell Referring to Itself 06-04-2010, 03:07 PM
Crisi88 Re: Cell Referring to Itself 06-04-2010, 03:27 PM
romperstomper Re: Cell Referring to Itself 06-04-2010, 03:36 PM
foxguy Re: Cell Referring to Itself 06-04-2010, 04:11 PM
  1. #1
    Registered User
    Join Date
    06-04-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Cell Referring to Itself

    Hi, I'm writing a function in excel that is a string converter. Essentially it takes information in a given set of cells and then encrypts them into a code.

    So F2 = StringConv(A2,B2,C2,D2,E2) would return "12345" depending on cells A2 through E2.

    I have it set up in such a way that if the data enter isn't valid it would return a question mark where the invalid data is.

    So if there is bad data in B2, then F2 = StringConv(A2,B2,C2,D2,E2) would return "1?345"

    What I want to do now is add conditional formatting into the function. So the function would read its own output and change the font to Bold and Red.

    Function StringConv(Type As String, Class As String, p3 As String, p4 As String, Optional p5 As String, Optional p6 As String, Optional p7 As String) As String

    Dim OutType As Integer
    Dim OutClass As Integer


    Select Case Type
    'LINE
    Case "L"
    OutType = "1"
    OutClass = Classifier(In_Class)
    OutLine = LineCoding(p3, p4, p5, p6, p7)
    StringConv = ""
    StringConv = OutType + OutClass + OutLine
    GoTo CodingEnd

    CodingEnd:
    End Function

    Okay so I've emmitted a lot of the code because of confidentiallity reasons, but this should suffice. There are a few more Cases but they are set up in the exact same fashion. Classifier returns a string and LineCoding returns a string.

    So StringConv = OutType + OutClass + OutLine retruns a string. I want to check if this string contains a question mark so "~?" and if so turn the Cell's font colour to Red and Bold the font.

    I'm well aware of

    ( ).Font.Color = RGB(255, 0, 0)
    ( ).Font.Bold = TRUE

    My problem is I do not know how to reference the cell that the function is located in. The function will not always be in column F and it will not always be in the column directly after variable input. This is my real problem, finding the question mark should be pretty easy with something like:

    If InStr(strInput, "~?") > 0 Then
    ( ).Font.Color = RGB(255, 0, 0)
    ( ).Font.Bold = TRUE
    End If

    I'd also not want to have to have another variable selecting the cell that the function is in. If its any help, the first cell I select is always in Column A.

    Anyways any help is much appriciated!

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Cell Referring to Itself

    Your function cannot change the properties of the cell it's in. Why not just use conditional formatting normally?
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    06-04-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Cell Referring to Itself

    Quote Originally Posted by romperstomper View Post
    Your function cannot change the properties of the cell it's in. Why not just use conditional formatting normally?
    Oh really? Is there no ability for a function to change the Cell that it is in? What if I use a separate function and refer my function to it?

    ChangeFont(StringConv) As String

    I'm not the only one using this Formula, and I will not be using any longer after I finish my work term at my company. So my boss has asked me to do it automatically using the function. This is what I was using in Conditional Formatting:

    =NOT(ISERR(SEARCH("~?",T9)))

    Which works fine, but my boss needs to use this on a bunch of different Workbooks and within the Workbooks on multiple spreadsheets with different number of columns. He doesn't want to have to do it everytime.

    So there is absolutely no way to automate this? Is there a way using a function to automatically change the conditinoal formating of say the last nonblank column?

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Cell Referring to Itself

    No, that won't work either. Worksheet functions are limited in what they can do, and they are not allowed to alter the Excel environment other than to return a value to a cell. (there are certain exceptions to this, but they are not officially supported, and this isn't one of them - you can actually add a conditional format condition, but you can't apply any formatting!)

  5. #5
    Registered User
    Join Date
    06-04-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Cell Referring to Itself

    Quote Originally Posted by romperstomper View Post
    No, that won't work either. Worksheet functions are limited in what they can do, and they are not allowed to alter the Excel environment other than to return a value to a cell. (there are certain exceptions to this, but they are not officially supported, and this isn't one of them - you can actually add a conditional format condition, but you can't apply any formatting!)

    Oh! Well how can I add a Conditional Format Condition? I mean if I add a Conditional Format Condition to the range of Cells using VBA that's all I really need

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Cell Referring to Itself

    I think you misunderstood me - the function could add conditional formatting to the cell but not apply any formatting. In other words, the CF would test the cell value, but then not do anything.

  7. #7
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Thumbs up Re: Cell Referring to Itself

    Crisi88;

    Quote Originally Posted by romperstomper View Post
    I think you misunderstood me - the function could add conditional formatting to the cell but not apply any formatting. In other words, the CF would test the cell value, but then not do anything.
    I got around this limitation once a long time ago, but I don't remember exactly how.
    But I can get you started. I'm pretty sure I just had to tab away from the worksheet then tab back.
    I don't remember the syntax for adding a conditional format, but you'll want to look that up yourself anyway
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        Dim rCell as Range
    
        For Each rCell in Sh.UsedRange
            With rCell
                If .Formula = "myUDF()" Then
                    With .FormatConditions.Add()
                    End With
                End If
            End With
        Next rCell
    End Sub
    I also remember that I put some flag somewhere in the worksheet that let me know that I'd already checked this sheet, so I didn't have to check it each time it activated.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1