Where do I put all the Dim sets? I noticed you started the Sub after them....


Bob Phillips wrote:
I am not really sure what you are asking, but perhaps this will help

Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")

sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value < sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value < sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to run a
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1