Why doesn't the font color change?

[Ingeniero1]

With Dominic’s help, I added the Function “Protection()” to m
spreadsheet, which function displays the protection status for th
sheet. It updates only when a value is entered or when F9 is pressed
and that suits my application fine.

The function:

FUNCTION PROTECTION()
APPLICATION.VOLATILE TRUE
PROTECTION = \"UNPROTECTED\"
IF ACTIVEWORKBOOK.PROTECTSTRUCTURE = TRUE OR _
ACTIVESHEET.PROTECTCONTENTS = TRUE THEN
PROTECTION = \"PROTECTED\"
END IF
END FUNCTION

I also wanted to change the font color of the cell according to status
Unprotected would be red, and protected would be green.
For that, I tested the following macro, and it works fine when run as
macro; i.e., the fonts of the cells change to red and green.

SUB COLORINDECES()
CELLS(1,1).FONT.COLORINDEX = 3
CELLS(2,1).FONT.COLORINDEX = 4
END SUB

However, if I add the 'font-color lines' of the macro to the function
as shown below, the font colors don’t change. Why?

FUNCTION PROTECTION()
APPLICATION.VOLATILE TRUE
PROTECTION = \"UNPROTECTED\"
CELLS(1, 1).FONT.COLORINDEX = 3 'DISPLAY UNPROTECTED IN RED
IF ACTIVEWORKBOOK.PROTECTSTRUCTURE = TRUE OR _
ACTIVESHEET.PROTECTCONTENTS = TRUE THEN
PROTECTION = \"PROTECTED\"
CELLS(1, 1).FONT.COLORINDEX = 4 'DISPLAY PROTECTED IN GREEN
END IF
END FUNCTION


Thanks!

Ale

 

[Bernie Deitrick]

Alex,

Functions can't change anything other than the value that they return to the
calling cell: not the color, not the font, nothing. You would need to use
either conditional formatting, a macro, or an event to change the color.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Functions used in worksheet cells are not permitted to alter the excel
environment. They only return values to the cells, like any worksheet
function.

You can use conditional formatting under the format menu to change the font
color base on the value returned by your function.

 

[Bob Phillips]

Is this what you want

Function PROTECTION()
Application.Volatile True
With Cells(1, 1)
.Value = "UNPROTECTED\"
.Font.ColorIndex = 3 'DISPLAY UNPROTECTED IN RED
If ActiveWorkbook.ProtectStructure = True Or _
ActiveSheet.ProtectContents = True Then
.Value = "PROTECTED\"
.Font.ColorIndex = 4 'DISPLAY PROTECTED IN GREEN
End If
End With
End Function

 

[dominicb]

Good afternoon Alex

You can't do this within a UDF I'm afraid. A UDF differs from a macro
in that you cannot change or format the sheet in any way. All you can
do is perform a calculation and insert it into a cell. However, you
could set your conditional formatting to change the cell a different
colour depending on the cell contents.

HTH

DominicB

 

[Ingeniero1]

Dominic, Bernie, Tom -
Conditional formatting works fine. I just wanted to see if it could b
done from within the function itself - now I know better.
Thanks!

Tom,
I tried that, but XL gave me a 'circular reference' error when
entered the function reference at cell A1, and a 'Value' error whe
entered elsewhere.

Thanks all the same.

Ale

 

[Bob Phillips]

Sorry, missed the F9 bit.

--
HTH

Bob Phillips

Is this what you want

Function PROTECTION()
Application.Volatile True
With Cells(1, 1)
.Value = "UNPROTECTED\"
.Font.ColorIndex = 3 'DISPLAY UNPROTECTED IN RED
If ActiveWorkbook.ProtectStructure = True Or _
ActiveSheet.ProtectContents = True Then
.Value = "PROTECTED\"
.Font.ColorIndex = 4 'DISPLAY PROTECTED IN GREEN
End If
End With
End Function


--
HTH

Bob Phillips

in message news:[email protected]...

 

[Tom Ogilvy]

Ingeniero,
I didn't offer anything but correct advice.

I think you want to "thanks all the same" to Bob.

No sense in disrespecting me.

 

[Tom Ogilvy]

Hit the button too fast

"No sense in disrespecting me."

should have been

No sense in disrespecting me. <g>

Sorry

--
Regards,
Tom Ogilvy

Ingeniero,
I didn't offer anything but correct advice.

I think you want to "thanks all the same" to Bob.

No sense in disrespecting me.