Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    add color depending on the statment (Excel 2000)

    Anyone know if its at all possible to make a selection of cells different color depending on the word inside them?

    attached is a wrokbook which has 2 worksheets.
    worksheet 1 hads a combo box which chooses from list a

    the is a second list List b which I use as a Vlookup table.

    currently I am just using Vlookup to fill in the range of cells depending on whats been picked in the combo box.
    which has a different arrangement of words.
    right now I can only keep the range cell one color grey, but was hoping someone might know a way where I can have different colors for different words.

    the three words are Lead, Tail, Mud
    the colors I am trying to get for each word is
    Dark Grey for Tail
    Mid Grey for Lead
    light grey for Mud.

    any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: add color depending on the statment (Excel 2000)

    Which cell or cells do you want to color, and how exactly? The words occur in the shaded merged cell H9-H50 on Stage 1 Job Info, but it may contain more than one of the words at a time. Please try to be precise.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add color depending on the statment (Excel 2000)

    yes i am thinking that depending on the statement chosen from the combo box would decide which cells to color in.
    there is 11 statements
    I have put a dash (-), after the word which indicates a different color.
    for example if in the combo box the choice Lead, Tail and Mud is made then
    range H9 to H19 = light grey
    range H20 to H37 Mid Grey
    Range H38 to H50 Dark grey.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add color depending on the statment (Excel 2000)

    I am not conpletely clear on what you desire.

    If you want 'Stage 1 Job Info'!H9:H50 to be different colors in different parts, you will need to not merge the cells, but keep each "section" separate and use a vlookup for each section and then perhaps use a conditional format for each section and color (if you want more than 3 colors in the future it will be a problem)

    Another option would be to use a modified stacked column for each part and have the color of each part. This would take some "revamp" to calculate the data for the chart but could be setup to do the various sections and even handle more colors, but how well it works will depend on how complicated the stages are They could be setup in columns of tail, lead, Mud, tail, lead, Mud to handle what you have, though more might be needed if you have other cases. (I put a possible "example" of this technique)

    If you could elaborate on what you want/need, other ideas may come out...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add color depending on the statment (Excel 2000)

    ok, i think this might be more of a precise way of explaining what i am try to do,
    I have added a table
    basically i think the range for each header will need to be merged and then color based on the word that will showup? not sure??
    the table is on sheet1 starting on F7.
    and on the same line as the condition

    thanks Hans for having a look,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: add color depending on the statment (Excel 2000)

    I'd go with Steve's suggestion of a stacked column chart.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add color depending on the statment (Excel 2000)

    wow Steve it works nicley and the panic of buiding a macro was what i wa worried about.

    Thanks both of you for the help.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add color depending on the statment (Excel 2000)

    one question though steve <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    how did you get the values in the graph to be words and not numbers?

    maybe a quick run through of what you did would help

    thanks again

    spa

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add color depending on the statment (Excel 2000)

    In D6:I6 enter:
    Tail, Lead, Mud, Tail, Lead, Mud

    In each row of D7:I17 enter the appropriate values. Each row should add up to 1
    In row 7(eg) you have Tail at bottom and Lead at Top so enter 0.5 in each of them

    The rest are all based on your "description" in Col C.

    In C1 get the "row value" of the selection with the formula:
    =MATCH(B3,listA,0)

    To Set up chart in each of D1:I1 enter (we will change later)
    =1/6

    In D2 enter:
    =IF(D1>0,D6,"")
    Copy D2 to E2:I2

    Select D1:I1
    Insert - chart
    Chart Type: Column
    Chart subtype: Stacked Column
    <Next>
    Series in: "Column"
    <finish>

    Dbl-Click series 1:
    Patterns(tab): Area: choose "Dark Grey"
    Data Labels(tab): "Show Value"
    Options(tab): Gap WIdth: 0
    <ok>

    Dbl-Click series 2:
    Patterns(tab): Area: choose "Mid Grey"
    Data Labels(tab): "Show Value"
    <ok>

    Dbl-Click series 3:
    Patterns(tab): Area: choose "light Grey"
    Data Labels(tab): "Show Value"
    <ok>

    Repeat for Series4,5,6 making dark, Medium, light grey.

    Select the legend <delete>
    Dbl-click Y-axis
    Patterns (tab): click None for each item
    Scale(tab): Maximum:1
    <ok>
    Dbl-click X-axis
    Patterns (tab): click None for each item
    <ok>

    Click twice on the datalabel in Series 1 (1st click puts 2 black squares to right and left, second puts a square around the number)
    Then in formula bar enter:
    =Sheet1!$D$2

    Click twice on the datalabel in Series 2, and in formula bar enter:
    =Sheet1!$E$2

    Click twice on the datalabel in Series 3, and in formula bar enter:
    =Sheet1!$F$2

    Click twice on the datalabel in Series 4, and in formula bar enter:
    =Sheet1!$G$2

    Click twice on the datalabel in Series 5, and in formula bar enter:
    =Sheet1!$H$2

    Click twice on the datalabel in Series 6, and in formula bar enter:
    =Sheet1!$I$2

    Dbl-Click the series 1datalabel and on Alignment (tab), set orientation to 90 degrees

    Repeat for series 2-6

    Adjust the chart size and font size as desired.

    In D1 enter the formula:
    =INDEX(D717,$C$1)

    Copy D1 to E1:I1

    Now when you change the combobox, changes the chart as desired

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add color depending on the statment (Excel 2000)

    Thank you very much

Posting Permissions

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