Re: Check values cells with Formula or vba?
Hi aisietie,
It looks from your example as though there will not be zeros in the first cells, with 1s subsequently. If this is the case, then try in the relevant cells of column W:
=--(SUM($T$2,$T$22,$T$42,$T$62,$T$82,$T$102,$T$122,$T$142)=((ROW()+18)/20))
although I'm not sure why the final cell in column W is W142 and not W122.
Hope this helps.
Re: Check values cells with Formula or vba?
Not nearly as compact as Batman's, but it seems to work.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestValue As String
Dim A As Long
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Sheet1.Range("A2:G2")) Is Nothing Then
For A = 1 To 7
TestValue = TestValue & Sheet1.Cells(Target.Row, A)
Next
Application.EnableEvents = False
Sheet1.Range("W2,W22,W42,W62,W82,W102,W122") = 0
Select Case TestValue
Case "1000000"
Sheet1.Range("W2") = 1
Case "1100000"
Sheet1.Range("W22") = 1
Case "1110000"
Sheet1.Range("W42") = 1
Case "1111000"
Sheet1.Range("W62") = 1
Case "1111100"
Sheet1.Range("W82") = 1
Case "1111110"
Sheet1.Range("W102") = 1
Case "1111111"
Sheet1.Range("W122") = 1
End Select
End If
End If
Application.EnableEvents = True
End SubRe: Check values cells with Formula or vba?
Just another way of doing the same thing. 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr$
Set myRng = Range("T2, T22, T42, T62, T82, T102, T122")
If Intersect(Target, myRng) Is Nothing Then Exit Sub
For Each cRng In myRng
myStr = myStr & cRng
Next
Range(Replace(myRng.Address, "T", "W")) = 0
Select Case InStr(1, myStr, "0")
Case 0
[W122] = 1
Case 1
Case Else
Range("W" & ((InStr(1, myStr, "0") - 1) * 20) - 18) = 1
End Select
End SubRe: Check values cells with Formula or vba?
If vba, try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Not Intersect(Target, Range("T2,T22,T42,T62,T82,T102,T122")) Is Nothing Then
Application.EnableEvents = False
Range("W2,W22,W42,W62,W82,W102,W122").Value = 0
For Each r In Range("T122,T102,T82,T62,T42,T22,T2")
If r.Value = 1 Then
r(, 4).Value = 1: Exit For
End If
Next
Application.EnableEvents = True
End If
End SubRe: Check values cells with Formula or vba?
Have you tried my original solution when the first value is zero? It seems to work. Although you don't say, I assume that if the T2 value is zero, the result in W2 also needs to be zero.
However, an alternative formula (provided that there is nothing in cell T142) would be =IF(AND(T2=1,OFFSET(T2,20,0)=0),1,0).
Both of these are based on the assumption that you will not have any of the earlier cells with a value of zero, followed by later cells with a value of 1, e.g. 0-0-1-1-0-1-0.
Don’t have an account yet? Register yourself now and be a part of our community!