Font Chance to Cell Contaning the UDF

[AT--o]

VBA Code:

[pre]

Function MyExample (mystring1 As String)
Dim        mynumber As Single

If             mystring1  = "A" Then
mynumber  = 1
Else
mynumber  = 2
End If

If             mynumber  = 1 Then
'What I want to do is change the font color of the cell containing this UDF
ActiveCell.Font.Color = RGB(255, 0, 0) 

End If
MyExample =  mynumber
End Function

[/pre]
This works but I have to make the cell active (I know there might be another way to make it active but I double click the cell as for editing it and the press enter). After doing that the color is changed accordingly but I have too many cells for manually activating each. I also tried:

Cell.Font.Color = RGB(255, 0, 0) and

ThisCell.Font.Color = RGB(255, 0, 0)

But in both cases not even the function value works. I cant use the conditional formatting since the function is not as simple as for this explanation expressed. I dont think selection or range would apply when what I want to change is the cell that contains the UDF, I mean Im not selecting it in any way, Am I?. I hope I have been clear enough. Thank you all.

 

[Hui]

AT


Try this

[pre]

Function My(mystring1 As Range)

Dim mynumber As Single

mystring1.Offset(0, 1).Font.Color = RGB(0, 0, 0)

If mystring1.Value = "A" Then
mynumber = 1
mystring1.Offset(0, 1).Font.Color = RGB(255, 0, 0)
Else
mynumber = 2
End If

My = mynumber

End Function

[/pre]

 

[Hui]

AT--o


Try the following:

[pre]

Function My(mystring1 As Range)
Application.ThisCell.Font.Color = RGB(0, 0, 0)

If mystring1.Value = "A" Then
My = 1
Application.ThisCell.Font.Color = RGB(255, 0, 0)
Else
My = 2
End If

End Function

[/pre]

 

[AT--o]

Sorry it took me this long, I really am since your code was exactly what I needed. Thank you very much.