Locking Cells

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:
>
> Hi
> Please help me for following questions :
> 1. Function/marco/formula to unlock only 'Blue Text' and all other should be
> locked so that nobody can change the formula or overwrite the formulas
> 2. Function/Macro/Formula to see only result of formula, no formula could be
> seen by unauthorized person.
>
> Please help
> Thanks
> Jai

--

Dave Peterson

Hi Dave,
Thanks
Option 2 is working for me but then I am unable to write in Blue cells.
Option 1 macro, I tried it, but I am not sure why it's not working.
Please help.
THanks
Jai

"Dave Peterson" wrote:

> #1. With your worksheet unprotected:
>
> Option Explicit
> Sub testme()
> Dim myCell As Range
> Dim myRng As Range
>
> With ActiveSheet
> Set myRng = .UsedRange
> .Cells.Locked = True
> End With
>
> For Each myCell In myRng.Cells
> If myCell.Font.ColorIndex = 5 Then
> myCell.Locked = False
> End If
> Next myCell
>
> End Sub
>
> #2. When you lock the cells, you can hide the formulas.
> Format|cell|protection tab|check locked and hidden.
>
> Then protect the worksheet.
>
> ps. Worksheet protection is very, very easy to break.
>
> Jai wrote:
> >
> > Hi
> > Please help me for following questions :
> > 1. Function/marco/formula to unlock only 'Blue Text' and all other should be
> > locked so that nobody can change the formula or overwrite the formulas
> > 2. Function/Macro/Formula to see only result of formula, no formula could be
> > seen by unauthorized person.
> >
> > Please help
> > Thanks
> > Jai
>
> --
>
> Dave Peterson
>

There are lots of different shades of blue.

Select one of those blue cells and hit alt-f11 (to get to the VBE).

Then hit ctrl-g to see the immediate window.

Type this and hit enter:

activecell.font.colorindex

Use that same number (I used 5, who knows if that was correct) in that routine.

Jai wrote:
>
> Hi Dave,
> Thanks
> Option 2 is working for me but then I am unable to write in Blue cells.
> Option 1 macro, I tried it, but I am not sure why it's not working.
> Please help.
> THanks
> Jai
>
> "Dave Peterson" wrote:
>
> > #1. With your worksheet unprotected:
> >
> > Option Explicit
> > Sub testme()
> > Dim myCell As Range
> > Dim myRng As Range
> >
> > With ActiveSheet
> > Set myRng = .UsedRange
> > .Cells.Locked = True
> > End With
> >
> > For Each myCell In myRng.Cells
> > If myCell.Font.ColorIndex = 5 Then
> > myCell.Locked = False
> > End If
> > Next myCell
> >
> > End Sub
> >
> > #2. When you lock the cells, you can hide the formulas.
> > Format|cell|protection tab|check locked and hidden.
> >
> > Then protect the worksheet.
> >
> > ps. Worksheet protection is very, very easy to break.
> >
> > Jai wrote:
> > >
> > > Hi
> > > Please help me for following questions :
> > > 1. Function/marco/formula to unlock only 'Blue Text' and all other should be
> > > locked so that nobody can change the formula or overwrite the formulas
> > > 2. Function/Macro/Formula to see only result of formula, no formula could be
> > > seen by unauthorized person.
> > >
> > > Please help
> > > Thanks
> > > Jai
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson