Excel Data Range problem

[scrappe7]

Hello, I am using a spreadsheet which as the label for all the columns has a specific work week. The spreadsheet then has a "bar" with a project name and a color code that extends over the duration of the number of work weeks it will take to complete. I need to refernce the amount of work weeks a project is taking into another work sheet that produces a number summary for me. I've tried using count, countA but it will only read the first cell that contains the project title and will not count the continuation of bars for each project. For example, if a project takes 3 weeks it fills the length of 3 columns. the countA function will only return a value of 1, instead of the 3 that i need since it occupies 3weeks. can anyone help me??? any help or better approaches to perform this would be greatly appreciated. Thanks in advance.

 

[ilses]

Could you please explain what the "bars" consist of?
Are these colour coded cells, using a fill colour applied directly to the cell or perhaps fill colours originating from conditional formatting? Or something altogether different ... And, is the project name in a cell to the left of the "bar"?

IS

 

[Databaseguy]

Assuming you're using something like a red fill in a cell, try something like this:

Private Sub CountRed()

Dim counter As Integer
Range("a1:A1").Select
Range("a1:A1").Activate
For counter = 1 To 10 '(Or however many columns you have)
ActiveCell.Select
If Selection.Interior.ColorIndex = 3 Then  '3 is red. Change as you see fit
counter = counter + 1
End If
ActiveCell.Offset(1, 0).Activate
Next counter
Range("Number").Select 'Name a range to store your value
Selection.Value = counter
End Sub

 

[scrappe7]

Thanks guys but I've gone at the problem from a completly different approach. the bars were just color coded to let you know. I really appreciate the help and I'm sure I'll be asking for some more soon.

Thank you!