Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula help ? (XP)

    Or maybe it's formatting help I need...

    Anyway, trying to apply cell shading based on the contents of "a combination" of results in other cells. Sounds complicated, I know. I've attached my work in progress with callouts to explain what I'm attempting. Callout #7 is where I'm bogged down. Suggestions appreciated.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

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

    Re: Formula help ? (XP)

    I don't understand why your callout states that Thursday afternoon should be shaded.

    I have attached my attempt, using conditional formatting with a formula.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula help ? (XP)

    I'm not sure what you did, but it's working perfectly! I'm going to dig into the conditional formatting to see how you did it.

    If I can figure that part out, I think later, I'll look into making the schedule work directly from the original input data and avoid all that extra tooling around on the bottom half of the sheet. I'm thinking it should be possible to shade the appropriate cells directly within the input grid, making 2/3 of what I have now un-needed. And, it can probably be accomplished without macros...Yeah...

    But for now, you've provided me with a model that works and I thank you. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

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

    Re: Formula help ? (XP)

    I selected B14:H18, selected Format | Conditional Formatting, selected Formula Is and entered the following formula:

    =VLOOKUP(LEFT(B$3,3)&" "&LEFT($A4,FIND(" ",$A4)-1),$D$23:$H$57,5,FALSE)="SCHEDULE"

    Finally, I clicked Format and set a background color, then OKed my way out.

    The above formula is for B14; it is automatically propagated for the other cells in the selection. Note the use of absolute and relative references.

    LEFT(B$3,3) extracts the abbreviation for the day name in B3; it results in "Mon".
    LEFT($A4,FIND(" ",$A4)-1) extracts the first word in A4; it results in "Breakfast".
    These are concatenated to "Mon Breakfast".
    VLOOKUP looks up this concatenated value in D23:H57, and returns the corresponding value in the 5th column, i.e. "SCHEDULE" or 0.
    This is compared to "SCHEDULE", yielding either TRUE or FALSE. This controls the conditional formatting.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula help ? (XP)

    Thanks for explaining. Very cool!
    - Ricky

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula help ? (XP)

    Uh-oh, new problem.

    If I change the row height of rows 22 thru 59 to zero (in an effort to hide the "behind the scenes" calcs), it causes the macro to produce weird results. Restoring the row heights to "visible", fixes it right up... Why should it matter what the row height is set at?

    If I just change all the text color to white to make the calcs invisible, it still leaves the area open to manipulation. If I use white text and apply protection, then the macro can't write to a protected area, so that won't work...

    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    Can I add something to the macro that will unprotect the sheet, sort the numbers, and then re-protect the sheet before giving it back to the user?
    I'm checking <font color=blue>Search</font color=blue> now...
    - Ricky

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

    Re: Formula help ? (XP)

    Hidden rows are not sorted, see MSKB article XL: Sorting Feature Ignores Hidden Rows. As a workaround, unhide the rows, sort them, then hide them again. By turning off screen updating temporarily, the user won't even see this. The code can be made slightly more efficient by not changing the selection for each action.

    Sub Button2_Click_SortList()
    Application.ScreenUpdating = False
    Range("A23:B57").Copy
    Range("D23").PasteSpecial Paste:=xlPasteValues
    Range("22:59").EntireRow.Hidden = False
    Range("D23:E57").Sort Key1:=Range("E23"), Order1:=xlDescending, _
    Header:=xlNo, Orientation:=xlTopToBottom
    Range("22:59").EntireRow.Hidden = True
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula help ? (XP)

    Thanks Again. I replaced my code with the code you provided and it solves all the issues. I made a few cosmetic changes and it's ready to distribute.

    Incidently, the code I was using was derived from the use of macro recorder. It's neat how you were able to eliminate certain lines and other parts of the original code (thus, making it more efficient). It would seem that Excel should do a better job with efficiency on it's own. I'm sure on this particular exercise it's not a major ordeal...but on a more extensive project, I bet the extra, un-needed code could lead to a performance problem.

    ...Anyway - I think it's my turn to contribute something worthwhile. So, it's off to the puzzle board! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula help ? (XP)

    Wow! I just turned BRONZE and there were no party horns, noise-makers or confetti... <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    - Ricky

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

    Re: Formula help ? (XP)

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> OK then, congratulations! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  11. #11
    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: Formula help ? (XP)

    <hr>It would seem that Excel should do a better job with efficiency on it's own<hr>
    I think this is asking a lot from what is essentially a "stupid computer program" (as they all are). I am amazed that the macro recorder can do what it does at all. It goes well beyond just mimicking the keystrokes.

    The recorder is a very "basic VB tool". It is great for learning the objects to manipulate, but it is a poor alternative for doing any "real coding": it does create inefficiency (one of the biggest flaw is its use of "SELECT"), it grabs all settings from dialog boxes (not just the ones you set/change), it can not do any LOOPS (Do .. Loop, For ... Next, etc) or use IF statements to compare and act differently under different circumstances, and it does not use variables.

    In some circumstances the recorder can save a lot of time and do 90% of the coding. At other times, it can only do 10% and you would be better off just coding it from scratch. In almost all instances, you should review the code to understand what the recorder is putting down and (in almost all cases) you should modify it.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>,
    Steve

Posting Permissions

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