Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Highlighting Rows (excel 2002/xp)

    In the attached cell A10 on the first sheet has been highlighted in yellow. Is there a formula or code that can add 14 days to A10 through the rest of the workbook and highlight the appropriate date. In the example A10 has January 13. The next one to be higlighted should be January 27, then go to the next worksheet and highlight the 14th day from January 27 etc. I've included 2 monthss, but this will need to go through all 12 months.

    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    What determines the first cell to be hilited (in your example A10)? This might be done in several ways, but I would need to know how the starting cell is determined. Would it be acceptable to enter the starting date in another fixed cell? If so, what cell would be preferable?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    I just used a background fill to highlight the first cell. It is the first day of a schedule for leave that occurs every 14 days from that date. It could go into any other cell after column H. It is just a method of highlighting those days that a person is off because of a flexible work schedule.

    Thanks.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    OK, that is the reason for my question. There is no way to determine in a worksheet formula that a cell is hilited, that would take VBA code. Even then the code would be a bit unreliable. If you hilited in a different color (a lighter yellow for example), the code would not know what was happening. I could write some VBA code that would find the first cell that had a background color and then hilite all cells that are a multiple of 14 days from that cell. However, that would cause problems if you need to use different background colors for other things. If you can put the starting date into a cell, I think I can use conditional formatting to hilite all cells that are a multiple of 14 days from that date. Changing a cell's background color does not raise any events, so this will not be automatic unless you use a cell to enter the starting date.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    OK. What I would see then is using conditional formatting that would look at the cell in question (a10) add 14 to it and then the corresponding cell in column H (h10) put something like LEAVE with a background color. I did this for column A in a test and could get the second date (January 27th) to be highlighted, but I could not get it to work on the second sheet which would have Feb 10th and 24th highlighted.

    Thanks.

    Hope this makes sense.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    The attached workbook has named cell A29 on the Jan sheet StartDate. That cell is then used in conditional formatting to hilite the the cell whose date is entered into cell A29 and every 14 days from that date.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    Thanks

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    I have tried your suggestion. The first date highlights but not the others. I have attached my workbook. Have created a StartDate in the appropriate cell and entered the conditional formatting. I copied your formula and changed to the appropriate cell. What in world am I doing wrong?

    Added: Note I made a correction in the workbook attached. I copied formula down the column in January (This doesn't show up in the attached). My working copy now shows January works fine,. How do I get it to go to the other sheets without having to enter the formula on each sheet and and copy it down?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    You only applied the conditional format to that one cell. I selected all of the sheets, then selected cells A2:A24 (the largest ranged used on any of the sheets), and then applied the conditional formatting that is shown for cell A2 on the Jan sheet. This applied the formula to all of the cells on all of the sheets.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlighting Rows (excel 2002/xp)

    I was just going to correct myself again, but you replied. I did find my stupidity. Thanks.

Posting Permissions

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