Hello Jeff,
Thanks for replying. I apologize for calling "Sop_Col" "S_Col" and "Alt_Col" "A_Col". The applicable column headers are "Soprano" and "Alto". The criteria range "S_Rng" is defined by variables. The column is the one in which the heading is "Soprano" is found in the Find statement. The top row of S_Rng is 2 (always) while the bottom row is LRow as defined by [A500].end(xlup).row. So, the full definition of S_Rng is Cells(2, Sop_Col).Address & ":" & Cells(LRow, Sop_Col).Address. The same method applies to "A_Rng". S_Rng really stands for "Soprano Range" and A_Rng really stands for "Alto Range". Each row is the recond of a person. If the person sings both soprano and alto, the two cells are highlighted. The code does the same thing (except for using different colors) for those who sing 1) Alto and Tenor, 2) Tenor and Baritone, 3) Baritone and Bass or 4) Tenor, Baritone and Bass.
After the highlighting is done I try to insert the COUNTIFS formula to count the number of people who indicated they can sing two or three vocal parts. Everything executes properly until the line where I am trying to code the formula. At that point I get a "Variable not Defined" or something like that. I am struggling with the syntax of the formula. If I could get it right, I'd rather use A1 style than R1C1, because I am much more familiar with it. The sticking point is where the criteria is "=Yes". If the person sings soprano, there's a "Yes" in the column. If she sings alto, there's a "Yes" in that column. I do not generate the spreadsheet. It is run by a third-party on a weekly basis from a church festival registration data base. Since the reports vary in the number of records I built in the flexibility to define the last record as being in the row called "LRow". The columns are the same in each weekly report but I find it easier to work with a macro when it identifies a column as something like "Sop_Col" rather than "V" or "22".
Below is the whole sub, with various attempts at the formula code included but commented out. This includes several lines copied from a recoded macro that I tried to modify.
Sub Highlight_2_Or_More_Voice_Parts()
Const S = "Soprano"
Const A = "Alto"
Const T = "Tenor"
Const B1 = "Baritone"
Const B2 = "Bass"
Dim V As Integer, Str, S_Rng, A_Rng As String
LRow = [B500].End(xlUp).Row
Rows("1:1").Select
Sop_Col = Selection.Find(What:=S, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
Alt_Col = Selection.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
Ten_Col = Selection.Find(What:=T, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
B1_Col = Selection.Find(What:=B1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
B2_Col = Selection.Find(What:=B2, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
For V = 2 To LRow
If Cells(V, Sop_Col).Value = "Yes" And Cells(V, Alt_Col).Value = "Yes" Then
Range(Cells(V, Sop_Col), Cells(V, Alt_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Ten_Col).Value = "Yes" And Cells(V, B1_Col).Value = "Yes" And Cells(V, B2_Col).Value = "Yes" Then
Range(Cells(V, Ten_Col), Cells(V, B2_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Alt_Col).Value = "Yes" And Cells(V, Ten_Col).Value = "Yes" Then
Range(Cells(V, Alt_Col), Cells(V, Ten_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Ten_Col).Value = "Yes" And Cells(V, B1_Col).Value = "Yes" Then
Range(Cells(V, Ten_Col), Cells(V, B1_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, B1_Col).Value = "Yes" And Cells(V, B2_Col).Value = "Yes" And Not Cells(V, Ten_Col).Value = "Yes" Then
Range(Cells(V, B1_Col), Cells(V, B2_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
GoTo 10
End If
10:
Next V
S_Rng = Cells(2, Sop_Col).Address & ":" & Cells(LRow, Sop_Col).Address
A_Rng = Cells(2, Alt_Col).Address & ":" & Cells(LRow, Alt_Col).Address
'The following is part of one attempt to rectify the problem:
Str = "="
Str = Str & "Yes"
'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & "," = "Yes""," & A_Rng & "," = "Yes"")"
'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & "," & Str & "," & A_Rng & "," & Str & ")"
'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & ",=" & Chr(34) & "Yes," & Chr(34) & "," & A_Rng & "=" & Chr(34) & "Yes" & Chr(34) & ")"
Cells(LRow + 2, Sop_Col).Select
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C:R[-4]C,""=Yes"",R[-69]C[1]:R[-4]C[1],""=Yes"")"
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C:R[-6]C,""=Yes"",R[-69]C[1]:R[-6]C[1],""=Yes"")"
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[-65]C:R[-2]C,""Yes"",R[-65]C[1]:R[-2]C[1],""Yes"")"
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R[" & -(LRow) & "]C:R[-2C,""Yes"",R[" & -(LRow) & "C[1]:R[-2]C[1],""Yes"")"
'=COUNTIFS($V$2:$V$65,="Yes",$W$2:$W$65,="Yes")
'cells(LRow,Sop_Col).formula = "=COUNTIFS(" & cells(2,LRow).address & ",""=Yes"" & cells(2,Alt_Col) & ",""=Yes"")"
'=COUNTIFS(V$2:V65,"=Yes",W2:W65,"=Yes")
End Sub
I hope you can identify
