EXCEL - Conditional Format based on another cell? 3

[Kenja824]

We have one cell ie... 1A, that is always the word "LEGACY" or GLOBAL".
I would like to do one of two things....

1) Have cell 7C lock if cell 1A says "GLOBAL" and be unlocked if 1A says "LAGACY".

(Im pretty sure that one cant be done, so I go to the conditional formatting idea....

2) Have cell 7C format with a color fill when cell 1A says "GLOBAL" but no formatting when cell 1A says "LEGACY"

Is this possible?

 

[electricpete]

Yes you can conditional format based on an expression...that expression can refer to other cells

=====================================
(2B)+(2B)' ?

 

[Kenja824]

Sorry for making this difficult. I click on Conditional Formatting - New Rule - "Use a formula to determine which cells to format" .... But then I cannot figure out what the correct formula is to type in the space.

I was thinking of an IF statement. =IF(1A="GLOBAL",?)... then I couldnt figure out what to use in place of the question mark.

I tried doing it without that but it doesnt work. I am either way off target, or just putting one small thing wrong each time I try, but I havent been able to get it where it works.

 

[zelgar]

You don't make an IF statement. Here's what you'd put in the Formula
=A1="GLOBAL"
Click the Format button and select Fill to determine the color

 

[JG2828]

See attached. Is this what you are trying to do?

 

[JG2828]

If you really want to use the IF statement, then you could use one of the following.

=IF(1A="GLOBAL",1,0)
or
=IF(A1="GLOBAL",TRUE,FALSE)

however Zelgar's answer is more to the point.

=A1="GLOBAL"

 

[Kenja824]

Thankyou very much. Yes Zelgar's answer worked perfect. I will give the IF statements a check too, just for knowledge reasons. lol

 

[onlyadrafter]

Hello,

you originally asked for a cell to be locked. This can be done, although your sheet protection and cell protection could affect the outcome.
I have removed cell protection from all cells and have password protected the worksheet with no password.

This code needs to go into the relevant sheet code window, not a standard module

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        ActiveSheet.Unprotect
        Select Case Target.Value
            Case "GLOBAL"
                Range("C7").Locked = True
            Case "LAGACY"
                Range("C7").Locked = False
        End Select
    End If
    ActiveSheet.Protect
End Sub

What, if anything, do you want to happen if cell A1 does not equal GLOBAL and does not equal LAGACY?

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[Kenja824]

It really wouldnt matter what happens if it is neither. I have a form that needs to be filled out and we found when doing Global information, certain specifications are not ever used, so it is a waste of time for someone to find the info and fill it into those cells when it wont even be used. And Global is almost all we use now. So I just wanted the cells to gray out as soon as they filed out it was a Global product. Since all of them are either global or legacy, it would only mean the sheet is not filled in properly if it is not set. In that case I would rather them fill out the extra info just to be safe.

In short, if it says Global, I want it to gray out (or lock the cells out) and anything else leave the cells untouched and unlocked.

Thanks Onlyadrafter. This is also very helpful.

 

[onlyadrafter]

Hello,

should have mentioned this only works when cell A1 is changed. I guess you have a formula. Try this

Private Sub Worksheet_Change(ByVal Target As Range)
        ActiveSheet.Unprotect
        Select Case Range("A1").Value
            Case "GLOBAL"
                Range("C7").Locked = True
            Case "LAGACY"
                Range("C7").Locked = False
        End Select
    ActiveSheet.Protect
End Sub

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[Kenja824]

Actually, I just used 1A as an example. But it is always the same cell changing. I would just change that part of the code to the cell I need.

Thanks