EXCEL - Conditional Format based on another cell?
EXCEL - Conditional Format based on another cell?
(OP)
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?
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?





RE: EXCEL - Conditional Format based on another cell?
=====================================
(2B)+(2B)' ?
RE: EXCEL - Conditional Format based on another cell?
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.
RE: EXCEL - Conditional Format based on another cell?
=A1="GLOBAL"
Click the Format button and select Fill to determine the color
RE: EXCEL - Conditional Format based on another cell?
RE: EXCEL - Conditional Format based on another cell?
=IF(1A="GLOBAL",1,0)
or
=IF(A1="GLOBAL",TRUE,FALSE)
however Zelgar's answer is more to the point.
=A1="GLOBAL"
RE: EXCEL - Conditional Format based on another cell?
RE: EXCEL - Conditional Format based on another cell?
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
CODE
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 SubWhat, 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
RE: EXCEL - Conditional Format based on another cell?
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.
RE: EXCEL - Conditional Format based on another cell?
should have mentioned this only works when cell A1 is changed. I guess you have a formula. Try this
CODE
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
RE: EXCEL - Conditional Format based on another cell?
Thanks