INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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?

RE: EXCEL - Conditional Format based on another cell?

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

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

RE: EXCEL - Conditional Format based on another cell?

(OP)
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.

RE: EXCEL - Conditional Format based on another cell?

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

RE: EXCEL - Conditional Format based on another cell?

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"

RE: EXCEL - Conditional Format based on another cell?

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

RE: EXCEL - Conditional Format based on another cell?

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

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 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

RE: EXCEL - Conditional Format based on another cell?

(OP)
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.

RE: EXCEL - Conditional Format based on another cell?

Hello,

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?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close