VBA: how to... cell properties has to change based on a condition

[will99]

Hi Everyone,

i'm new to this forum and i have a question for you reguarding excel VBA. I want to declare in a module a function or sub that will change the CELL properties where the function has been called from if another cell contains certain information...example:

A B C D
1 100 5.5 =ONOFFCell(A1)
2 0.5 2 =ONOFFCEll(A2)
3 .
4 .

the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that people cannot enter anything in C1 cell IF the cell A1 value let say is bigger than "0". Then on each line i will have to repeat the same function etc. etc.

How can i do that? Can someone help me ?

Thank you!

William!

[Ian]

Unlock the cells you need to edit usign Format>Cells>Protection>uncheck
Locked. Protect the worksheet and use this routine. Change the range for r
to suit your application. If the value in column 1 (A) is >0 the
corresponding cell in column 3 (C) is locked.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(1).Unprotect
For r = 1 To 10
If Cells(r, 1).Value > 0 Then
Cells(r, 3).Locked = True
Else
Cells(r, 3).Locked = False
End If
Next
Worksheets(1).Protect
End Sub

--
Ian
--
"will99" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Everyone,
>
> i'm new to this forum and i have a question for you reguarding
> excel VBA. I want to declare in a module a function or sub that will
> change the CELL properties where the function has been called from if
> another cell contains certain information...example:
>
> A B C D
> 1 100 5.5 =ONOFFCell(A1)
> 2 0.5 2 =ONOFFCEll(A2)
> 3 .
> 4 .
>
> the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that
> people cannot enter anything in C1 cell IF the cell A1 value let say is
> bigger than "0". Then on each line i will have to repeat the same
> function etc. etc.
>
> How can i do that? Can someone help me ?
>
> Thank you!
>
> William!
>
>
> --
> will99
> ------------------------------------------------------------------------
> will99's Profile:
> http://www.excelforum.com/member.php...o&userid=26790
> View this thread: http://www.excelforum.com/showthread...hreadid=400416
>

[Vacation's Over]

Generally a function returns a value and a sub does work (change formatting)

You could possibly use a function to call a sub but i think it would need to
know both the cell "A1" address and the address where the function is.

My preference for this type of thing would be to use the worksheet change
event
when Target is in Column A
then if target > 0 then
reset properties for target.offset(0,2)
reset could lock cell and change color of background to emphisize the lock.

"will99" wrote:

>
> Hi Everyone,
>
> i'm new to this forum and i have a question for you reguarding
> excel VBA. I want to declare in a module a function or sub that will
> change the CELL properties where the function has been called from if
> another cell contains certain information...example:
>
> A B C D
> 1 100 5.5 =ONOFFCell(A1)
> 2 0.5 2 =ONOFFCEll(A2)
> 3 .
> 4 .
>
> the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that
> people cannot enter anything in C1 cell IF the cell A1 value let say is
> bigger than "0". Then on each line i will have to repeat the same
> function etc. etc.
>
> How can i do that? Can someone help me ?
>
> Thank you!
>
> William!
>
>
> --
> will99
> ------------------------------------------------------------------------
> will99's Profile: http://www.excelforum.com/member.php...o&userid=26790
> View this thread: http://www.excelforum.com/showthread...hreadid=400416
>
>

[will99]

Generally a function returns a value and a sub does work (change formatting)

You could possibly use a function to call a sub but i think it would need to
know both the cell "A1" address and the address where the function is.

My preference for this type of thing would be to use the worksheet change
event
when Target is in Column A
then if target > 0 then
reset properties for target.offset(0,2)
reset could lock cell and change color of background to emphisize the lock.

Can you please show it to me with an example in real code?

Thank you!

William!