1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 & Excel 2003

Discussion in 'Business Applications' started by computerman29642, Oct 10, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is there a way to clear, and add Fill color to an Excel spreadsheet? Please see the attached file.

    I need this to be compatible with Excel 2003 & 2007.
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The easiest way I know is to use the free (and wonderful) add-in for Excel, ASAP Utilities
    http://www.asap-utilities.com/
    Part of the utility under the "Columns and Rows" selections it "Color each N'th row or column in selection" - just highlight your sheet and it will clear any existing colors and replace.
     
  3. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I'm not clear on your question. Simply selecting 'No fill' from the Fill Color drop down clears?
     
  4. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have seen it done by VBA before, but I am unsure how to get started.
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
  6. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Thanks draceplace. I will have a look.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    C-man, are you thinking of conditional formatting?
    That is, if the answer is negative, color the cell red, or something like that? Very easy to do - no VBA required.
     
  8. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I am just trying to highlight every other row. The reason I am trying to do it through VBA is becuase the users give me ID numbers out of order.

    If this is done through VBA, I can clear the Fill color, and then re-apply.
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, if, for instance, you want all the even numbered rows colored green, you could easily set up conditional formatting to do that based on the ID numbers - then sort the data by the ID numbers and every other one would be colored green, assuming that you have all the ids (and if you don't or have duplicates, it would be a rather obvious marker that something was wrong).
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    That sounds like it would work. I have the workbook setup to not allow duplicates.

    Now if I add an ID out of order at the very bottom, will the conditional formatting handle that? I have a macro now that sort sthe data when the workbook is opened.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    For instance, if you go to Format, Conditional Formatting after highlighting the sheet and enter this
    Formula Is: =$A1/2-ROUND($A1/2,0)=0
    then select your format, only rows with even numbers in column A (Assuming that is where you IDs are) will be highlighted.
    One advantage of this is you can sort later and the formatting will stay with the row. If you color every other row and then sort the sheet later, the formatting will move with the rows also.
    However, if you happen to sort only a few columns, all the coloring will move with conditional formatting, while the colors will only shift in those few columns if you have "hard-formatted" the color.
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The CF just looks at the value, not the location. So if, say, you are coloring even numbers and the id at the bottom is odd, it will not be colored, even if it is in an even row. And vice-versa.
    Incidentally, my earlier formula colors the even numbers. To do it for odds, change it to
    =$A1/2-ROUND($A1/2,0)<>0
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    For some reason, it is working for the lower half of the spreadsheet, but not the upper portion.
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    First, do you have any even numbers in the upper half?
    Did you highlight the whole sheet when you entered the CF?
    Did you have some other CF entered previously? If so, it could be overriding the new CF (with multiple CFs, the first one is carried out, then the next and so on - but not if the second would override the first, or the 3rd the 2nd...kind of like Asimov's 3 rules of robotics.... ;) )
     
  15. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have a number like 92025 in the upper half.

    Yes, I highlighted from A3 to the end of the worksheet (A3 is where the ID start).

    No, I do not have any other CF.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757816

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice