Is this possible?

[TyeJae]

Here is the formula I have right now:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For x = 6 To 18
If Cells(x, 3).Value = 1 Then
Range(Cells(x, 13), Cells(x, 13)).Value = "1"
Range(Cells(x, 14), Cells(x, 32)).Value = "-"
End If
Next x

Given this information:
C6 = 1
C7 = 0
C8 = 0
C9 = 0
C10 = 1
C11 = 0
C12 = 1
C13 = 0
C14 = 0
C15 = 1
C16 = 0
C17 = 0
C18 = 0

The first time there is a "1" in these cells I want it to run:

For x = 6 To 18
If Cells(x, 3).Value = 1 Then
Range(Cells(x, 13), Cells(x, 13)).Value = "1"
Range(Cells(x, 14), Cells(x, 32)).Value = "-"
End If
Next x

The second time there is a "1" I want it to do this:

For x = 6 To 18
If Cells(x, 3).Value = 1 Then
Range(Cells(x, 13), Cells(x, 22)).Value = "-"
Range(Cells(x, 23), Cells(x, 23)).Value = "1"
Range(Cells(x, 24), Cells(x, 32)).Value = "-"
End If
Next x

Then next time there is a "1" I want it to run the the first formula
and so on back and forth, is this possible?

-TyeJae

 

[Charles]

Tye
Try this
Not tested

dim me as boolean
me = false

For x = 6 To 18
If Cells(x, 3).Value = 1 and me = false Then
me = true
Range(Cells(x, 13), Cells(x, 13)).Value = "1"
Range(Cells(x, 14), Cells(x, 32)).Value = "-"
End If
Next x

The second time there is a "1" I want it to do this:

For x = 6 To 18
If Cells(x, 3).Value = 1 and me = true Then
Range(Cells(x, 13), Cells(x, 22)).Value = "-"
Range(Cells(x, 23), Cells(x, 23)).Value = "1"
Range(Cells(x, 24), Cells(x, 32)).Value = "-"
End If
Next x

Charle

 

[TyeJae]

Charles,

When I put that formula in it gives me a pop up that says:

Compile Error:
Expected: identifier

Then it highlights "Dim Me As Boolean"

-TyeJae

 

[TyeJae]

Is there a way to have it count how many ones are from C6:C18 and for
every odd "1" it runs the first formula then for every even "1" it runs
the second formula?

-TyeJae-

 

[Charles]

OOp's

Sorry can not use Me. Change to "m" and use this code.


For x = 6 To 18
If Cells(x, 3).Value = 1 And m = False Then
Range(Cells(x, 13), Cells(x, 13)).Value = "1"
Range(Cells(x, 14), Cells(x, 32)).Value = "-"
m = True
ElseIf Cells(x, 3).Value = 1 And m = True Then
Range(Cells(x, 13), Cells(x, 22)).Value = "-"
Range(Cells(x, 23), Cells(x, 23)).Value = "1"
Range(Cells(x, 24), Cells(x, 32)).Value = "-"
End If
Next x

Hope this is what you are looking for

Charle

 

[TyeJae]

Very nice, thank you tons!

 

[Tom Ogilvy]

Don't you need a

m = False

in the elseif part of your conditional?

For x = 6 To 18
If Cells(x, 3).Value = 1 And m = False Then
Range(Cells(x, 13), Cells(x, 13)).Value = "1"
Range(Cells(x, 14), Cells(x, 32)).Value = "-"
m = True
ElseIf Cells(x, 3).Value = 1 And m = True Then
Range(Cells(x, 13), Cells(x, 22)).Value = "-"
Range(Cells(x, 23), Cells(x, 23)).Value = "1"
Range(Cells(x, 24), Cells(x, 32)).Value = "-"
m = False
End If
Next x

 

[Charles]

Tom,

Yes the "m =False" should have been there. I hope Tye noticed.

Charle