Peanutmc
08-04-2010, 08:00 AM
Hey gang,
Working on a few spreadsheets for a work project and in an effort to be more organized I find myself scratching my head on this one. It's been a while since I've done any excel macros so hopefully someone can help.
I'm trying to create a macro that will check for Ys in one worksheet, and, if all fields contain a Y, then an entry is filled in on another worksheet. Here is my code...
Sub Run()
Dim Multi As Range
Set Multi = Range("D3:D400")
For Each Row In Multi
Sheet1.Activate
If Row = "N" Then
Row.Offset(0, 1) = ""
Multi.Select
Multi.Offset(0, 1).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Row.Offset(0, 2) = ""
Multi.Select
Multi.Offset(0, 2).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Row.Offset(0, 3) = ""
Multi.Select
Multi.Offset(0, 3).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
ElseIf Row = "Y" Then
Sheet2.Activate
If Row = "Y" And Row.Offset(0, 1) = "Y" And _
Row.Offset(0, 2) = "Y" And Row.Offset(0, 3) = "Y" And _
Row.Offset(0, 4) = "Y" And Row.Offset(0, 5) = "Y" And _
Row.Offset(0, 6) = "Y" And Row.Offset(0, 7) = "Y" Then
Sheet1.Activate
Row.Offset(0, 1) = "Y"
End If
End If
Next Row
End Sub
Working on a few spreadsheets for a work project and in an effort to be more organized I find myself scratching my head on this one. It's been a while since I've done any excel macros so hopefully someone can help.
I'm trying to create a macro that will check for Ys in one worksheet, and, if all fields contain a Y, then an entry is filled in on another worksheet. Here is my code...
Sub Run()
Dim Multi As Range
Set Multi = Range("D3:D400")
For Each Row In Multi
Sheet1.Activate
If Row = "N" Then
Row.Offset(0, 1) = ""
Multi.Select
Multi.Offset(0, 1).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Row.Offset(0, 2) = ""
Multi.Select
Multi.Offset(0, 2).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Row.Offset(0, 3) = ""
Multi.Select
Multi.Offset(0, 3).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
ElseIf Row = "Y" Then
Sheet2.Activate
If Row = "Y" And Row.Offset(0, 1) = "Y" And _
Row.Offset(0, 2) = "Y" And Row.Offset(0, 3) = "Y" And _
Row.Offset(0, 4) = "Y" And Row.Offset(0, 5) = "Y" And _
Row.Offset(0, 6) = "Y" And Row.Offset(0, 7) = "Y" Then
Sheet1.Activate
Row.Offset(0, 1) = "Y"
End If
End If
Next Row
End Sub