Changing font color of protected cells programatically

[Nigel]

I am not sure if the problem is directly related to upgrading to Excel 2003,
or a setting in Excel which has been changed due to the upgrade.

The app is full of macro's as well as having VBA code. All cells are
protected except for those used for user-input. The cell in question is a
calculated value. If it exceeds a certain value it must change from red to
blue. This is done in VBA.

This has always worked, until the upgrade to 2003.

Is there a security setting which can be used to allow VBA to change the
font color of the protected cell, or am I going to have to use VBA to
unprotect the cell, change the font color, and protect the cell again?

Nigel

 

[Dave Peterson]

I don't know what (if anything) changed between xl2003 and your previous
version, but this sounds like it's the perfect situation to use
format|conditional formatting. You wouldn't need VBA at all (for the color
change).

 

[Nigel]

I don't know what (if anything) changed between xl2003 and your previous
version, but this sounds like it's the perfect situation to use
format|conditional formatting. You wouldn't need VBA at all (for the color
change).

Could do that - problem is that then 25 copies of the modified spreadsheet
must be distributed around the country!! That is why I am hoping for a
settings solution.

Also, why did it used to work. And will conditional fromatting work with
older versions of Excel, for the people who haven't upgraded yet?

Nigel

 

[Dave Peterson]

What version of excel where you using?

What was the code that did the actual work?

Did you unprotect or protect the worksheet to allow the program to do things
that the user can't (using the userinterfaceonly parm on the .protect
statement)?

Did the cell get locked on that protected sheet?

======
xl2007 changed conditional formatting quite a bit. Before that, all versions of
excel that supported conditional formatting (xl97-xl2003???) were the same.

 

[Nigel]

Please remember that this has always worked. This app has been used since
about 1997, through many upgrades, and many versions of Excel, probably
starting with '95. Although the font colour change was not originally there -
a letr innovation.

I also have vague recollections of the problem occuring with one or two
users at various times, but I have no ide how the problem was solved in those
cases.

The version of the app with the font colour change has been running ob '98,
'2000 and for all I know there are useres with '2003 who don't have the
problem. I am not fully up to date as I retired the end of last year and it
is only one user who contacted me about it. Checking on my own PC I can
replicate the problem.

The code snippet is:
If Range("SchedHrs").Offset(X, 1).Value <> "" Then
If Range("SchedHrs").Offset(X, 1).Value < Cells(4, 66).Value Then
Range("SchedHrs").Offset(X, 1).Font.Color = vbRed
Else
Range("SchedHrs").Offset(X, 1).Font.Color = vbBlue
End If

Admittedly, there is a chance that on the copies where the code does work,
that the worksheet in question is not protected. Unprotecting the sheet makes
the code work. Also, when protecting the sheet, if I allow the user to format
cells, then the code works.

NB It seems to be that Excel applies the protection level of formatting
cells to changes made by code, as well as by the user.

The reference to Cell(4,66) is in any case incorrect, so the code doesn't
really work as is anyway, so they will have to modify the code and
redistribute the templates anyway.

Which probably means that I will have to do it, as I don't think there is
anyone in the company that has ever used VBA!!!

Nigel

 

[Nigel]

Another thought has come to me: People were using the template just fine.
They made a backup of the template, were given new PC's with new versions of
Office, put the template back into XLStart, and it no longer works.

Has to be either Excel 2003, or a setting. There are changes to Excel in the
way of using templates so it could very well be 2003's problem.

Nigel

 

[Dave Peterson]

I don't have another guess.

Another thought has come to me: People were using the template just fine.
They made a backup of the template, were given new PC's with new versions of
Office, put the template back into XLStart, and it no longer works.

Has to be either Excel 2003, or a setting. There are changes to Excel in the
way of using templates so it could very well be 2003's problem.

Nigel