Cell Referring to Itself

[Crisi88]

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!

[romperstomper]

Your function cannot change the properties of the cell it's in. Why not just use conditional formatting normally?

[Crisi88]

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?

[romperstomper]

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!)

[Crisi88]

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

[romperstomper]

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.

[foxguy]

Crisi88;

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

Please Login or Register  to view this content.

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.