• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Font Chance to Cell Contaning the UDF

AT--o

New Member
VBA Code:

[pre]
Code:
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.
 
AT


Try this

[pre]
Code:
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]
 
AT--o


Try the following:

[pre]
Code:
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]
 
Sorry it took me this long, I really am since your code was exactly what I needed. Thank you very much.
 
Back
Top