Solved: Excel 2007 & Excel 2003

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

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.
 
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?
 
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.
 

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.
 
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).
 

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.
 
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.
 
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
 
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.... ;) )
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top