Formatting Protected Cells (Excel 97 on Windows NT)

Topic

New Reply

A user in Finance wants to create a protected form that allows formatting changes. I can’t attach the file as it is too big.

UNlocked cells allow users to enter requisite data. The guy creating the form wants to allow people using the form to be able to add color to certain lines. But, when the worksheet is protected, all formatting options become unavailable to both locked and UNlocked cells.

Is there a way to protect the worksheet/book while continuing to allow certain cells to accept formatting changes? Specifically changing the background color of the cell?

Thank you for your help!

Reply | Quote

Replies

Meredith,

With a little programing code you can.

Paste the following code into a workbook module ( Alt + F11).
Change the “xxx” password in the code to the actual password.
On the spreadsheet, use the View | Toolbars | Forms menu to add a button and assign the procedure (code) to it.
The code unprotects the sheet, shows a formatting dialog box and then protects the sheet.
If a problem were to occur with the code then a message is displayed that says to contact you.

‘Code follows———————————————————————————————————-

Sub MakeItPretty()
On Error GoTo GotUgly
ActiveSheet.Unprotect password:=”xxx”
Application.Dialogs(xlDialogPatterns).Show
ActiveSheet.Protect password:=”xxx”
Exit Sub
GotUgly:
Beep
MsgBox “Error ” & Err.Number & ” ” & Err.Description & vbCr & _
“Please see Meredith if the problem persists. “, _
vbExclamation, ” Format Error Alert”
On Error Resume Next
ActiveSheet.Protect password:=”xxx”
End Sub
‘—————————————————————

Regards,

Jim Cone
San Francisco, CA

Reply | Quote

Thanks for the idea, Jim, but I’d rather not create a macro for this situation. Our users are not particularly experienced and if something went wrong, they’d be clueless.

Does this mean that a protected workbook/worksheet is not able to have the formatting changed? Again, I appreciate the assistance.

Reply | Quote

Yes, if you protect a worksheet you will lose some “functionality” even in the unprotected section/cells. Changing color is one of them. Autofilter (in XL97, at least) is also disabled.

The way around this is via a macro.

Reply | Quote

Hi Meredith,
If it’s acceptable for the colours of a range of cells to change to one of up to three pre-defined colours automatically, depending on the value entered in one or more of those cells, you could use conditional formatting to make the colour changes – or to change other formatting. This can be used to change anything from a single cell to a whole row, column or matrix or a series of them.
Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Please teach me how to use conditional formatting for an entire row! I have only been able to use conditional formatting on a single cell, but I found that very limiting!

Thank you, thank you, thank you!

Reply | Quote

Select the entire row by clicking the row number. Now you can set conditional formatting in one fell swoop. If you need conditional formatting based on a formula, enter the formula for the first cell in the row (in column A). Excel will automatically adjust it for the other cells in the row.

Reply | Quote

Oh Hans, I’m disappointed! Hopefully I’m doing something wrong.

I selected the entire row and set the conditional formatting. However, when I enter the value associated with the conditional formatting, only the cell with that value changes formatting. Is there a way for one cell to change the formatting of the whole row (without getting into macros?)?

Reply | Quote

Sorry, I misunderstood your question. If you want to have conditional formatting for all cells in an entire row depend on the value of a single cell, you must use a formula with the absolute address of that cell.

Say that you want row 4 to have a red background if cell A4 is negative:
Select row 4.
Select Format/Conditional Formatting…
From the first dropdown list, select Formula.
In the formula box, enter =$A$4<0
The $ signs indicate that this is an absolute cell address, not to be adjusted for the other cells in the row.
Set the format you wish
Click OK.

Reply | Quote

Works like a charm!

You rock!

Reply | Quote