Conditional Formating

[markvdh]

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?

 

[Tom Ogilvy]

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

 

[markvdh]

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

 

[avveerkar]

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

 

[Tom Ogilvy]

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

 

[markvdh]

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