Sheet protection versus changing text color

If the only reason you protected the sheet is so that no one could change that one formula then you could unprotect the sheet, then add code to the Worksheet_Change event that would always reset the formula back to what you want. Then add your code to change the cell color.

Other than that I don't know, sorry.

-Mike

Hi Wil,

When you protect the sheet you will have to select Format Cells under the "Allow all users of this worksheet to" box which is just below where you put the password in.

Brett

Thank you for replying BrettVBA but I lost you here...

Extra-->Protection-->Save sheet?

I do not see anything below the password.

Don't quite understand. Is cell A1 the cell you want colored text in? If so, why not pre-format the text color and then protect the sheet? Am I missing something?

It is not thát simple...

I have a sheet with monthly data.

At the beginning of the month I add data as a kind of prediction.

At the end of the month I check the data and if the prediction was correct leave the data as it is.

The predicted data starts in blue color to show it's status but will be changed by hand at the end of the month -if correct.

As this is not possible on a protected sheet I tried "bold" but this does not work either.

Howdy Wil, in xl 2000 you can apply font colour changes to locked and unlocked cells via vba. A quick way is:<pre>
activecell.font.colorindex = 3</pre>

You can't use Excel's built in dialog, Dialogs(161), which is dumb. But you can use the windows pallete, as below:<pre>
Private Declare Function ChooseColor Lib "comdlg32.dll" Alias _
"ChooseColorA" (pChoosecolor As ChooseColor) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Type ChooseColor
lStructSize As Long
hwndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Function ShowColor() As Long
Dim ChooseColorStructure As ChooseColor
Dim Custcolor(16) As Long
Dim lReturn As Long
ChooseColorStructure.lStructSize = Len(ChooseColorStructure)
ChooseColorStructure.hwndOwner = FindWindow("XLMAIN", _
Application.Caption)
ChooseColorStructure.hInstance = 0
ChooseColorStructure.lpCustColors = StrConv(CustomColors, _
vbUnicode)
ChooseColorStructure.flags = 0
If ChooseColor(ChooseColorStructure)<> 0 Then
ShowColor = ChooseColorStructure.rgbResult
CustomColors = StrConv(ChooseColorStructure.lpCustColors, _
vbFromUnicode)
Else
ShowColor = -1
End If
End Function

Sub ColorCell()
ActiveCell.Font.Color = ShowColor
End Sub</pre>

Spotted this gem here. Just tweaked the last piece to work on a font versus interior.

You can associate ColorCell with a button with or just run it. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-12-10 19:18

Adapting the above code led to

selection.font.colorindex = 3

And that worked fine for me.

My knowledge of VBA is not enough to understand the rest of the code. A little translation for further learning?
This message was edited by Wil Moosa on 2002-12-11 14:14

Howdy Wil, glad to hear some sort of solution is up and running. You're Welcome.

I at least recommend trying the second group of codes, I think you'll like this, it works better than fine, it's smokin'.

Basically your using api functions to call Windows dialgos, a color pallete in this case. The sub routine initiates the functions. A lot of the function goes towards placing the dialog in excel, vs your desketop, capturing your selection, etc... Put it in a dedicated module and run the last procedure.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-12-11 14:22

On 2002-12-10 13:13, Wil Moosa wrote:
Thank you for replying BrettVBA but I lost you here...

Extra-->Protection-->Save sheet?

I do not see anything below the password.

Click to expand...

Tools|Protection|Protect sheet|
then underneath where it says password to protect sheet you have a whole lot of check box's and you just have to check FORMAT CELLS.

Brett