+ Reply to Thread
Results 1 to 7 of 7

Conditional Format with drop down menu

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Conditional Format with drop down menu

    I have a spreadsheet that I would like to format based on the selection in column A from a drop down menu. I have been able to format lines 3-5 how I would like the line to appear once a selection has been made. But I would like to utilize the formulas for all the rows. My issue is that there are basically 3 different options of what a row could look like, how do i merge that so that each row can potentially look 3 ways depending on the dropdown.
    Copy of Sample WH Template v2 (3) (2).xlsx

    Thanks in advance!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional Format with drop down menu

    You have such a mishmash of conditional formatting it is hard to say what cells you want formatted how. Here's how to do it though.

    First Clear all your conditional formatting
    Select all the cells that you want to format based on k-1. Let's Say it's E2:F200 and G2:K200.
    Conditional Format> New Rule>Formula =$A2="k-1" Format appropriately. All the rows will format simultaneously
    Repeat with cells for "w/h" and "1042/8804"
    Is that what you were looking for?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-24-2012
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Conditional Format with drop down menu

    Not exactly. I want the formatting to basically apply to the whole sheet so that when I select k-1 or w/h from the drop down in column a of any row, it will look like either row 3, 4, or 5 depending on which one i select. Does that make sense? Right now I have the 3 rows formatted separately but I beleive I would need to combine them and then apply that to the whole sheet. Please let me know what you think.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional Format with drop down menu

    I assume we are looking at "Sample"worksheet. If I chose 1042/.. in row 3, E3 is grayed with red font and strikeout. With any other choice there is no formatting
    In row 4, this happens only with w/h and in row 5 happens only with k-1. However, with those same choices, T3 is formatted but T4 and T5 are not. Maybe if you write out what columns you want formatted based on what is in Col A (is this the only column affecting formatting?) I can tell you how to go about it.

    Okay, I think I get it now. Might be easiest to do this a column at a time. Select E3:E200 and format on the equation
    =OR($A3={"k-1", "w/h", 1042/8804"})
    For X3:X200, it would be
    =OR($A3={"w/h", 1042/8804"})
    and so on.
    You could figure out which columns would get the same formatting formula and do them simultaneously but probably safer to do one at a time.
    Does that help?
    Last edited by ChemistB; 10-24-2012 at 04:53 PM.

  5. #5
    Registered User
    Join Date
    10-24-2012
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Conditional Format with drop down menu

    Basically, if you look at worksheet "sample (2)", where rows 2, 4 and 6 have no are the columns that I would like formatted (crossed out in red with gray background). The formatting I want is different for each selection of the drop down which is what I'm having a hard time with.

    At the end of the day, I would like to have a spreadsheet in which all the fields are populated and I can go into column A of each row, select 'w/h' from the drop down of that row and have the cells crossed out if I don't need that info for w/h. In the same row I want to be able to select "k-1" and have different cells crossed out b/c that info doesn't relate to k-1s.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional Format with drop down menu

    Correction. I don't think Conditional Formatting allows Arraying {} so would need to make it
    =OR($A3="k-1",$A3= "w/h", $A3=1042/8804")

  7. #7
    Registered User
    Join Date
    10-24-2012
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Conditional Format with drop down menu

    Yes! that will work, it will just be a pain going through every column. But thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1