Excel VBA colour alternate rows [Solved/Closed]

tora - Jul 1, 2010 at 04:32 AM - Latest reply:  tora
- Jul 2, 2010 at 01:47 AM
Hello,

I am creating a report, and I want to shade alternate cells in a light grey colour. I need all columns and rows, starting at A5, that contains numbers to be filled. As I am adding information in the sheet everyday it expands, so I want the code to follow the expansion.

Also, I want row I5 to be red whenever the number is less than 0.

I have tried conditional formatting but it doesnt work!!

Thanks for the help!!




See more 

6 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 1, 2010 at 04:55 AM
0
Thank you
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link
0
Thank you
http://docs.google.com/...

Hope this helps

//Tora
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 1, 2010 at 07:09 AM
There is number only in last column titled Total. The other columns have either alphanumeric values or dates. Yet you say you want a cell to be highlighted if that cell contains a number
Row 2 contains Total values for the columns below.
Column I contains percentages. I want that value to have red font if it is a minus-values i.e. below 0.
And I need all rows that contains text or value to be alternating light grey and white ( in this document A5:L20.

I can't use conditional formatting because in excel 2002 condition 1 takes over condition 2. So I want to use VBA. But the problem is I'm not good at VBA so I don't know how to create the code.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 1, 2010 at 02:49 PM
For coloring the values when they are less than 0, you can use regular cell format. You have go to custom format and use this formula

This is for percentages
0.00%;[Red]0.00%

This is for numbers . This you would find under number too. The red one with numbers.
0.00;[Red]0.00

for alternate rows color issue, lets say if a row has data in its any cell, then on that row the column A, MUST and MUST have data too
in that case

Select from from A5 to to last row under column L
Then go to conditional format and enter
=AND($A5<> "", MOD( ROW($A5),2)<>0) and choose your light grey color. Only hitch in this that I can see is that this will color odd number of rows if they have data. So if there is in between blank A, your color scheme might not be what you wanted. What I mean is that if row 15 is grey , row 16 is white, row 17 is blank, then if you expect row 18 to be gray as it has data, then it will not work. It will make row 19 gray if that had data
Great!! Thanks :)