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

--
Ingeniero
-----------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...nfo&userid=402
View this thread: http://www.excelforum.com/showthread.php?threadid=37597

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


"Ingeniero1" wrote
in message ...

With Dominic's help, I added the Function "Protection()" to my
spreadsheet, which function displays the protection status for the
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 a
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!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=375973

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.

--
Regards,
Tom Ogilvy

"Ingeniero1" wrote
in message ...

With Dominic's help, I added the Function "Protection()" to my
spreadsheet, which function displays the protection status for the
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 a
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!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=375973

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

"Ingeniero1" wrote
in message ...

With Dominic's help, I added the Function "Protection()" to my
spreadsheet, which function displays the protection status for the
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 a
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!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=375973

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=375973

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

--
Ingeniero
-----------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...nfo&userid=402
View this thread: http://www.excelforum.com/showthread.php?threadid=37597

Sorry, missed the F9 bit.

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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

"Ingeniero1"
wrote
in message ...

With Dominic's help, I added the Function "Protection()" to my
spreadsheet, which function displays the protection status for the
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 a
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!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread:
http://www.excelforum.com/showthread...hreadid=375973

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.

--
Regards.
Tom govily

"Ingeniero1" wrote
in message ...

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

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

Thanks all the same.

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=375973

Hit the button too fast

"No sense in disrespecting me."

should have been

No sense in disrespecting me. <g

Sorry

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.

--
Regards.
Tom govily

"Ingeniero1"
wrote
in message ...

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

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

Thanks all the same.

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread:
http://www.excelforum.com/showthread...hreadid=375973