Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer


Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1


Exit Sub
End If
End If
Next i
End Sub

You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message
oups.com...
Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer


Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1


Exit Sub
End If
End If
Next i
End Sub

On May 17, 11:47 pm, "NickHK" wrote:
You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message

oups.com...



Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1

Exit Sub
End If
End If
Next i
End Sub- Hide quoted text -

- Show quoted text -

Sorry for that!

The error message is
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Since I am not familiar with visual basic. It will be great if you can
help me out!

Thank you so much for your patience!

Look forward to hearing from you!

Jorge

Probably would not get any if Range(i, 6) was changed to Cells(i, 6). The
code from a previous post produced several error messages. Most were caused
by trying to use code in a Worksheet_SelectionChange that was structured for
standard module application and not for passing by val to Target as Range.
The full code would not execute.

"NickHK" wrote:

You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message
oups.com...
Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer


Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1


Exit Sub
End If
End If
Next i
End Sub

On May 18, 9:52 am, JLGWhiz wrote:
Probably would not get any if Range(i, 6) was changed to Cells(i, 6). The
code from a previous post produced several error messages. Most were caused
by trying to use code in a Worksheet_SelectionChange that was structured for
standard module application and not for passing by val to Target as Range.
The full code would not execute.



"NickHK" wrote:
You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message
roups.com...
Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1

Exit Sub
End If
End If
Next i
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for your input!

Even I changed the code from Private Sub
Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

It is still not working.

Please advise!

Thanks again!

I dabbled with it a little last night trying to use the worksheet_change. I
am not sure how you are entering the color into the cell. If you are making
a manual entry, then would you want the value in col. F to change as you make
the entry, or would you want to make all of your entries and then run a macro
to check col. C & D and adjust the col. F value accordingly. One way would
take a Worksheet_Change event and the other could be done with a For
Each...Next loop.

" wrote:

On May 18, 9:52 am, JLGWhiz wrote:
Probably would not get any if Range(i, 6) was changed to Cells(i, 6). The
code from a previous post produced several error messages. Most were caused
by trying to use code in a Worksheet_SelectionChange that was structured for
standard module application and not for passing by val to Target as Range.
The full code would not execute.



"NickHK" wrote:
You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message
roups.com...
Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1

Exit Sub
End If
End If
Next i
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for your input!

Even I changed the code from Private Sub
Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

It is still not working.

Please advise!

Thanks again!

On May 18, 11:59 am, JLGWhiz
wrote:
I dabbled with it a little last night trying to use the worksheet_change. I
am not sure how you are entering the color into the cell. If you are making
a manual entry, then would you want the value in col. F to change as you make
the entry, or would you want to make all of your entries and then run a macro
to check col. C & D and adjust the col. F value accordingly. One way would
take a Worksheet_Change event and the other could be done with a For
Each...Next loop.



" wrote:
On May 18, 9:52 am, JLGWhiz wrote:
Probably would not get any if Range(i, 6) was changed to Cells(i, 6). The
code from a previous post produced several error messages. Most were caused
by trying to use code in a Worksheet_SelectionChange that was structured for
standard module application and not for passing by val to Target as Range.
The full code would not execute.

"NickHK" wrote:
You keep posting the same question without making much progress on any of
them.
If it is a problem with seeing your posts/replies, do not use Google but a
"real" newsreader; Outlook Express, Agent etc.

Anyway, which error do you get with the code below ?
Hint: Add a debug.print i

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
End Sub

Also, check the help for Offset and Intersect.

NickHK

wrote in message
roups.com...
Hi,

I have two columns, say column C and D with unlimited rows. The text
or quantity starts in each row either from column C or Column D, but
not both. I will need to highlight the starting cell with green by
manually fill it with green to indicate that I need this row. Here is
an example:

If I highlight C23 as green, I would like to return a value of 1 in
cell F23,
If I highlight C25 as green, I would like to return a value of 1 in
cell F25,
If I highlight D28 as green, I would like to return a value of 1 in
cell F28, etc.

Anyway, any cell under column F must be 1 or 0 depending on the cell
color under Column C OR Column D (not both) in the SAME ROW. Please
note that I can randomly add some new rows in the middle of the
worksheet. Those new rows will follow the same rules. This means that
there is no fixed number of rows.

Any help will be highly appreciated! The following code is for your
reference!

Thanks,
Jorge

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

Range(i, 6).Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Range(i, 6).Value = 1

Exit Sub
End If
End If
Next i
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for your input!

Even I changed the code from Private Sub
Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

It is still not working.

Please advise!

Thanks again!- Hide quoted text -

- Show quoted text -

Thank you so much!

I definitely prefer the latter option --- I want to make all of my
entries first and then run a macro
to check col. C & D and adjust the col. F value accordingly.

Please try your best to help me on this!

Thanks again,