Help with macro applying to every cell in that row/column

[spiffmonkey1]

Hi, I have a macro-

Sub ChangecolorAnalysis()
'1 box colored G=G
If WorksheetFunction.CountIf(Range("D69:D80"), "yes") = 1 _
And WorksheetFunction.CountIf(Range("D69:D80"), "") = 11 Then
Range("D67") = "yes"
Else:
Range("D67") = "N/A"

So D67 changes to "yes" when there are 1 "yes" and 11 "". However, I need this macro to also apply to MANY COLUMNS, so that for example E67 and F67 will change to "yes" when there ranges 69:80 has 1 "yes" and 11 "".

Thanks so much!

 

[Sektor]

Worksheet formula:
=IF(AND(COUNTIF(D69:D80,"yes")=1,COUNTBLANK(D69:D80)=11),"yes";ERFC(NA()))

 

[spiffmonkey1]

Worksheet formula:
=IF(AND(COUNTIF(D69:D80,"yes")=1,COUNTBLANK(D69:D80)=11),"yes";ERFC(NA()))

The formula didn't work on excel and can you explain the end of the formula? I'm not sure if that answers my question.

 

[Sektor]

Sorry, we have ";" delimiter of paramters instead of ",". Here's corrected. Last returns requested N/A.
=IF(AND(COUNTIF(D69:D80,"yes")=1,COUNTBLANK(D69:D80)=11),"yes",ERFC(NA()))

 

[spiffmonkey1]

Ok so I can use that formula for every cell in the row i need
Awesome thanks a lot!

If I wanted to add more conditions like if Range D69:D80 had 1 "yes", 1 "no",and 10 "", how would I do that?

Thanks again!

 

[Sektor]

=IF(AND(COUNTIF(D69:D80,"yes")=2,COUNTBLANK(D69:D80)=10),"yes",ERFC(NA()))

 

[spiffmonkey1]

Ok so I can use that formula for every cell in the row i need
Awesome thanks a lot!

If I wanted to add more conditions like if Range D69:D80 had 1 "yes", 1 "no",and 10 "", how would I do that?

Thanks again!

Ok I just did-
=IF(AND(COUNTIF(G69:G80,"yes")=1,COUNTBLANK(G69:G80)=11),"yes",IF(AND(COUNTIF(G69:G80,"yes")=1,COUNTIF(G69:G80,"no")=1,COUNTBLANK(G69:G80)=10),"somewhat","N/A"))

It seemed to work

 

[Desu Nota from Columbus]

Sub ChangecolorAnalysis()
Dim r As Long
For r = 4 To 11
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "yes") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)) = "yes"
Else:
Range(Cells(67, r), Cells(67, r + 1)) = "N/A"
End If

Next r

End Sub

Works up to column K (11).

Change "For r = 4 to 11 " to apply this to more columns.

 

[spiffmonkey1]

Hi, so i have a macro-

Sub ChangecolorAnalysis()
'1 box colored G=G
If WorksheetFunction.CountIf(Range("D69:D80"), "yes") = 1 _
And WorksheetFunction.CountIf(Range("D69:D80"), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range("D69:D80")) = 3 Then
Range("D67", "E67") = "yes"
Else:
Range("D67", "E67") = "N/A"
End If
End Sub

So D67="yes" when there are 1 "yes", 8 "N/A", and 3 blanks. The problem is I want this to apply to MANY columns such as column E,F,G,H,I,J,K. The problem with this macro is that it only apples to one column specifically

Thanks Desu works! I had to change the macro a bit for multiple conditions-

Sub ChangecolorAnalysis()
Dim r As Long
For r = 4 To 11
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "yes") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)) = "yes"
Else:
Range(Cells(67, r), Cells(67, r + 1)) = "N/A"
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "yes") = 2 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "no") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 6 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)) = "no"
Else:
Range(Cells(67, r), Cells(67, r + 1)) = "N/A"
End If
End If
Next r
End Sub

 

[spiffmonkey1]

I have another quick question-
For- And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
If someone mistyped N/A as NA, i want that to also change the worksheet.

Thanks