Hello everybody
I am new to VBA and need some help. I am trying to set up a worsheet
that will give me a timeline using conditional formating as follows:
Col A will have a input in the range, say 1 to 10, Col B will have a
start date, Col C will have an end date, Col D some other info, and
from Col E to the end of the sheet (Col IV), I want to see a solid
colour reflecting from the start date to the end date. The colour must
be dependant on the number in Col A.
Can anybody help?

You would need code to do this since conditional formatting does not offer
the option to use variable shading.

--
Regards,
Tom Ogilvy




"markvdh" wrote:

Hello everybody
I am new to VBA and need some help. I am trying to set up a worsheet
that will give me a timeline using conditional formating as follows:
Col A will have a input in the range, say 1 to 10, Col B will have a
start date, Col C will have an end date, Col D some other info, and
from Col E to the end of the sheet (Col IV), I want to see a solid
colour reflecting from the start date to the end date. The colour must
be dependant on the number in Col A.
Can anybody help?

Hi Tom
Do you have any suggestions regarding the requisite code?
Regards,
Mark

I understand that you want all the cells in the row starting from Col E
to the end of the sheet to be shaded with a colour which depends on the
value in Col A in the same row. What do you mean when you add " I want
to see a solid colour reflecting from start date to end date"?
A V Veerkar

markvdh Wrote:
Hello everybody
I am new to VBA and need some help. I am trying to set up a worsheet
that will give me a timeline using conditional formating as follows:
Col A will have a input in the range, say 1 to 10, Col B will have a
start date, Col C will have an end date, Col D some other info, and
from Col E to the end of the sheet (Col IV), I want to see a solid
colour reflecting from the start date to the end date. The colour must
be dependant on the number in Col A.
Can anybody help?


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=545887

Here is some pseudo code.

assume sequential dates in row 1

Dim rngDate as Range, rngA as Range
Dim cell as Range, res as Variant, res1 as Variant
Dim rngA1 as Range
set rngDate = Range("E1:IV1")
set rngA = Range(.Cells(2,1),.Cells(Rows.count,1).End(xlup))
for each cell in rngA
res = application.Match(clng(cell.offset(0,1),rngDate,0)
res1 = application.Match(clng(cell.offset(0,2),rngDate,0)
if not iserror(res) and not iserror(res1) then
set rngA1 = range(rngDate(cell.row,res),rngDate(cell.row,res1) )
Select Case cell.Value
Case 1
rngA1.Interior.ColorIndex = 3
Case 2
rngA1.Iterior.ColorIndex = 20
. . .
other value case statements
. . .

Case 10
rngA1.Interior.ColorIndex = 15
Case Else
rows(cell.row).Interior.ColorIndex = xlNone
End Select
end if
Next


--
Regards,
Tom Ogilvy




"markvdh" wrote in message
ups.com...
Hi Tom
Do you have any suggestions regarding the requisite code?
Regards,
Mark

Hi aweerkar
Basically, I am trying to create timelines for a project with each row
indicating an element of work (my apologies, but I need to amend my
initial request as regards column & row numbering).
So for example, in Row 6 Col A, I enter a text description, Col B would
have a number input (from 1 to 10 or larger), Col C would require an
inputed start date, Col D an inputed end date, Col E some other
information and starting from Col F to the end of the row (Col IV) -
i.e. sequential dates - I need to be able to see the time line, the
colour of which would depend on the number inputed in Col B.
Example, Col A would be "Initiate project", Col B "1", Col C
"3/6/2006", Col D "5/6/2006" and Col E has a formula calculating the
number of days between start and end dates. Then if I started the time
line from the 1/6/2006, the cells Col F & G would be blank, those in
Col's H to J would be solid colour (colour depending on the input in
Col B) and from Col K to Col IV also blank.
I need to repeat the timeline for row's 2 downwards.
I hope my description of the spreadsheet is a bit clearer than mud!!
Can you help?
Regards,
markvdh