macro not applying to entire range

[bearsfan]

I'm new to VBA and everytime I think I have made progress I get stumped. Here
is my current issue.

In one column I have project release dates and I want to retrieve the date
from a cell in that column and scan a row of all available project release
dates. When it finds a match I want the search to stop and indicate the
release will be on that day. I have the design weeks and testing weeks in
two separate columns as well. I then want to highlight the design and testing
weeks preceeding the release date. I'm trying to automate a project mapping.
Here is the code I have. I recorded the last sub code to see if I could get
some ideas on how to write the code. Here is also a crude example of my
table. I suspect there is an easy answer to this I just don't know it. I can
email out the spreadsheet too if anyone is interested. I can't express how
much I would appreciate help with this.

Thank you,
BearsFan

10/9 10/16 10/17 10/24 10/31
11/6 11/13

Proj Rel.

10/24 Des Des Test Rel
11/6 Des Des Test Test
Rel
11/13 Des Des Des Test Test
Test Rel

Sub BuildProjMap()

'********************************************************************************
'*
'* This Macro builds the Project Map range of dates based on the
'* number of weeks of duration for ADB + Test + Release.
'*
'********************************************************************************
' *************************************************
' Set initial variables
' *************************************************

rowProjDataStart = 4
rowProjDataEnd = 60
colProjMapDataStart = 17
colProjMapDataEnd = 46

colTargetRelWeekValue = 16
colADBDurValue = 14
colTestDurValue = 15

' ActiveSheet = "Sheet1"
SearchString = Cells(rowProjDataStart, colTargetRelWeekValue).Value
StartRow = 3
StartCol = colProjMapDataStart



colReleaseColNum = 16 ' replace this with a lookup
colADBStartColNum = colReleaseColNum - Cells(rowProjDataStart,
colTestDurValue) - Cells(rowProjDataStart, colADBDurValue)
colTestStartColNum = colADBStartColNum + Cells(rowProjDataStart,
colADBDurValue)

End Sub
Sub SearchRows(ActiveSheet, SearchString, StartRow, StartCol,
TargetRowNumVarName)

Dim rng As Range

'Sheets("ActiveSheet").Activate

Cells(StartRow, StartCol).Select

Set rng = Cells.Find(what:=SearchString, after:=ActiveCell, _
LookIn:=xlValues, _
SearchOrder:=xlColumns)
If Not rng Is Nothing Then
TargetRowNumVarName = rngl.Row
Else
TargetRowNumVar = 0
End If

TargetRowNumVarName = ActiveCell.Row

End Sub

Sub SearchColumns(ActiveSheet, SearchString, StartRow, StartCol,
TargetColNumVarName)

'Sheets(ActiveSheet).Activate

Cells(StartRow, StartCol).Select
Cells.Find(what:=SearchString, after:=ActiveCell, LookIn:=xlValues, _
SearchOrder:=xlRows).Activate
TargetColNumVarName = ActiveCell.Column

End Sub



Sub JPDTest()
'
' JPDTest Macro
' Macro recorded 10/9/2006 by Neal Desai


ActiveWindow.SmallScroll ToRight:=3
Range("AI4").Select
ActiveCell.FormulaR1C1 = "Rel"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AH4").Select
ActiveCell.FormulaR1C1 = "Rel"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AG4").Select
ActiveCell.FormulaR1C1 = "Test"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AF4").Select
ActiveCell.FormulaR1C1 = "Test"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AE4").Select
ActiveCell.FormulaR1C1 = "Test"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AD4").Select
ActiveCell.FormulaR1C1 = "Test"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AC4").Select
ActiveCell.FormulaR1C1 = "ADB"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AB4").Select
ActiveCell.FormulaR1C1 = "ADB"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AA4").Select
ActiveCell.FormulaR1C1 = "ADB"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Z4").Select
ActiveCell.FormulaR1C1 = "ADB"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Z4:AC4").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("AD4:AH4").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("AI4").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End Sub

 

[Sandy]

check your (e-mail address removed) mail for a reply and
email address...

Sandy