Shading cells in a row based on another cell
 

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
+-------------------------------------------------------------------

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=53261

Shading cells in a row based on another cell
 

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 |
+-------------------------------------------------------------------+

--
cm_gmail
------------------------------------------------------------------------
cm_gmail's Profile: http://www.excelforum.com/member.php...o&userid=33451
View this thread: http://www.excelforum.com/showthread...hreadid=532619

Shading cells in a row based on another cell
 

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
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=532619

Shading cells in a row based on another cell
 

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

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=53261

Shading cells in a row based on another cell
 

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
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=532619

Shading cells in a row based on another cell
 

Got an example of what you think they may do?


--
cm_gmail
------------------------------------------------------------------------
cm_gmail's Profile: http://www.excelforum.com/member.php...o&userid=33451
View this thread: http://www.excelforum.com/showthread...hreadid=532619

Shading cells in a row based on another cell
 

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

--
hannon
-----------------------------------------------------------------------
hannons's Profile: http://www.excelforum.com/member.php...fo&userid=3346
View this thread: http://www.excelforum.com/showthread.php?threadid=53261

Shading cells in a row based on another cell
 

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


--
hannons
------------------------------------------------------------------------
hannons's Profile: http://www.excelforum.com/member.php...o&userid=33465
View this thread: http://www.excelforum.com/showthread...hreadid=532619

Shading cells in a row based on another cell
 

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

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=53261