Shading cells in a row based on another cell

[matpj]

Hi everyone,

I have a sheet that has seperate rows for task ETCs
followed by (on the same row) a column for days used, remaining etc an
then a column fo each day) - almost like a gant chart but in excel.

I have attached the spreadsheet to this post.

I need to program something that will go through each row, look at th
number in column B, ans then shade from column E onwards by tha
number...

in the top row I have manually shaded the cells required.

can anyoen suggest how I coudl acheive this with VBA?

thanks in advance,
Mat

+-------------------------------------------------------------------
|Filename: gantsheet.zip
|Download: http://www.excelforum.com/attachment.php?postid=4624
+-------------------------------------------------------------------

 

[cm_gmail]

Conditional formatting is what you want. It's under the Format menu.

I've added a simplified version of yours to get you started.

Enjoy!


+-------------------------------------------------------------------+
|Filename: gantsheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4625 |
+-------------------------------------------------------------------+

 

[matpj]

thanks for your reply, I have ajusted this slightly to use a pattern etc
- and it works great!!



I'm concerned that the end user will start to put dates rather than day
numbers as the column headings.
If this happens, the conditional formatting wont work.
do you (or anyone else) know of a way around that?

cheers,
Matt

 

[matpj]

thanks for your reply, I have ajusted this slightly to use a pattern et
- and it works great!!



I'm concerned that the end user will start to put dates rather than da
numbers as the column headings.
If this happens, the conditional formatting wont work.
do you (or anyone else) know of a way around that?

cheers,
Mat

 

[matpj]

thanks for your reply, I have ajusted this slightly to use a pattern etc
- and it works great!!



I'm concerned that the end user will start to put dates rather than day
numbers as the column headings.
If this happens, the conditional formatting wont work.
do you (or anyone else) know of a way around that?

cheers,
Matt

 

[cm_gmail]

Got an example of what you think they may do?

 

[hannons]

You could try using cell data validation and limit the type of data tha
cell(s) can hold.
It's located under Data >> Validation...

You could also use comments to remind the user what type of data i
requested.

Scot

 

[hannons]

You could try using cell data validation and limit the type of data that
cell(s) can hold.
It's located under Data >> Validation...

You could also use comments to remind the user what type of data is
requested.

Scott

 

[matpj]

thanks for your replies.

it seems that there is a bigger problem here.

the tasks (individual rows) will not all start at the same time.
It looks like I'm going to have to process each row in turn by startin
at column F and moving 1 cell to the right until I come across th
yellow shading - not sure what he code is for that colour.

then using the figure in column C for that row, apply a pattern fo
that number.

so logically the steps would be:

1. for each row in range
2. store value of column c as a variable.
3. start at column F and increment by one (do until) cell shading is
specified value
4. select that cell plus the number of cells equivalent to variabl
stored in '2'.
5. move to next row and repeat.

can anyone help me with the for each row and the stepping through th
row column by column...

thanks in advance,
Mat