Traversing columns in excel

[drgoatboy]

hi all,
I'm sure this is really easy but I am not a VBA expert.
Basically what I want to do is starting from a set cell I want to move the next column and colour that cell. And do this under condition in a for loop.
(what I am trying to do is make a Gantt chart)

I know how to set the colour in the cell, and I can move down cells in a range but not across columns.

An example of this would be.
I have set-up my excel spreadsheet with years going horizontally across the top. Say starting with 2003 and going on to 2050 with each column being a year.
On another worksheet I put in a start and finish date of 2005 and 2010 respectively.
I want to then colour all the cells 2005 to 2010 yellow.
I would start at the 2003 cell and check if the cell is between the two values 2005 and 2010 and if it is colour it yellow. Then move to the next column and do the check again, carrying on until I have been through all the dates.

I know I can use conditional formatting in excel but that only allows me 3 conditions and over time I will want many more than that.

Can anybody help me out please.

Thanks very much Goatboy

 

[SkipVought]

Hi Goatboy,

I assume that you will have columns of data that define the starting and ending point for the gantt bar for that ROW

I also assume that the Start Column is in Col 3 and the End Column is in Col 4

You could use an approch like this...

for each r in MyVerticalRange
  with r
    StartCol = Cells(.Row, 3).Value
    EndCol = Cells(.Row, 4).Value
    range(cells(.row, StartCol), cells(.row, EndCol)).Interior.ColorIndex = WhateverColorValue
  end with
next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[drgoatboy]

thanks for the quick response skip.
Does that not then go down the page? (Or am I being Dim?)
I was hoping to go across (horizontal)

If I have got the wrong end of the stick any chance you could include all the code...
Or is that just being cheeky...

 

[SkipVought]

Describe the source data that you need to process for each row. The source data that defines the across parameters.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[drgoatboy]

Thanks Skip,
I have thought about this a bit more and it makes sense.
I was not certain about the cells() function but I have worked it out I think.
I very handy tool that!!


Thanks again