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,

On May 18, 12:35 pm, "
wrote:
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,- Hide quoted text -

- Show quoted text -

Hi,

The following code is working now. Firstly I define a name of "Index"
to include cells F1:F500. However, there is only one drawback. It is a
little bit slow in updating the screen once I manually hightlight a
cell under column C or D as green. Is there anyway to prevent this?
Thanks,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("Index").Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1

End If
End If
Next i
End Sub

This assumes that you have already entered your green color in columns C and
D, and that you used the shade designated as ColorIndex (10). I made it so
that if you have less than or more than 500 rows, it will still work.

Put this in the standard module 1 of your VBA project window:

Sub FixColF()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("F1:F" & lstRw) = 0
For i = 2 To lstRw
If Cells(i, 3).Interior.ColorIndex = 10 Or Cells(i,
4).Interior.ColorIndex = 10 Then
Cells(i, 6) = 1
End If
Next
End Sub

I am assuming you know how to run the macro once it is installed.



" wrote:

On May 18, 12:35 pm, "
wrote:
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,- Hide quoted text -

- Show quoted text -

Hi,

The following code is working now. Firstly I define a name of "Index"
to include cells F1:F500. However, there is only one drawback. It is a
little bit slow in updating the screen once I manually hightlight a
cell under column C or D as green. Is there anyway to prevent this?
Thanks,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("Index").Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1

End If
End If
Next i
End Sub

On May 18, 6:56 pm, JLGWhiz wrote:
This assumes that you have already entered your green color in columns C and
D, and that you used the shade designated as ColorIndex (10). I made it so
that if you have less than or more than 500 rows, it will still work.

Put this in the standard module 1 of your VBA project window:

Sub FixColF()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("F1:F" & lstRw) = 0
For i = 2 To lstRw
If Cells(i, 3).Interior.ColorIndex = 10 Or Cells(i,
4).Interior.ColorIndex = 10 Then
Cells(i, 6) = 1
End If
Next
End Sub

I am assuming you know how to run the macro once it is installed.



" wrote:
On May 18, 12:35 pm, "
wrote:
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,- Hide quoted text -

- Show quoted text -

Hi,

The following code is working now. Firstly I define a name of "Index"
to include cells F1:F500. However, there is only one drawback. It is a
little bit slow in updating the screen once I manually hightlight a
cell under column C or D as green. Is there anyway to prevent this?
Thanks,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("Index").Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1

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

- Show quoted text -

Thank you so much for your input!

I did the following but it looks like the code is not working! Please
advise!

Step 1: I open a new workbook, right click the Excel Icon located on
the upper left corner\view code\Insert\Module\Copy and paste the code
into Module 1\Alt Q
Step 2:View\Toolbars\Forms\Button\Assign the Macro to the new button
Step 3: Manualy change some cell colors under column C and D to green
(ColorIndex 10). After I finished everything, I click the Macro
Button. However, nothing happens except I saw a 0 in cell F1.

I am not sure whether I did something wrong!

Thanks,

On May 21, 8:11 am, "
wrote:
On May 18, 6:56 pm, JLGWhiz wrote:





This assumes that you have already entered your green color in columns C and
D, and that you used the shade designated as ColorIndex (10). I made it so
that if you have less than or more than 500 rows, it will still work.

Put this in the standard module 1 of your VBA project window:

Sub FixColF()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("F1:F" & lstRw) = 0
For i = 2 To lstRw
If Cells(i, 3).Interior.ColorIndex = 10 Or Cells(i,
4).Interior.ColorIndex = 10 Then
Cells(i, 6) = 1
End If
Next
End Sub

I am assuming you know how to run the macro once it is installed.

" wrote:
On May 18, 12:35 pm, "
wrote:
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,- Hide quoted text -

- Show quoted text -

Hi,

The following code is working now. Firstly I define a name of "Index"
to include cells F1:F500. However, there is only one drawback. It is a
little bit slow in updating the screen once I manually hightlight a
cell under column C or D as green. Is there anyway to prevent this?
Thanks,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("Index").Value = 0
For i = 1 To Rows.Count
If Cells(i, 3).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1
Else
If Cells(i, 4).Interior.ColorIndex = 10 Then
Cells(i, 6).Value = 1

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

- Show quoted text -

Thank you so much for your input!

I did the following but it looks like the code is not working! Please
advise!

Step 1: I open a new workbook, right click the Excel Icon located on
the upper left corner\view code\Insert\Module\Copy and paste the code
into Module 1\Alt Q
Step 2:View\Toolbars\Forms\Button\Assign the Macro to the new button
Step 3: Manualy change some cell colors under column C and D to green
(ColorIndex 10). After I finished everything, I click the Macro
Button. However, nothing happens except I saw a 0 in cell F1.

I am not sure whether I did something wrong!

Thanks,- Hide quoted text -

- Show quoted text -

Try the following and hope it will solve your problem:

Sub Test()
Dim RngColF As Range
Dim i As Range
With Worksheets("Sheet1")
Set RngColF = .Range("F1", .Range("F" & Rows.Count).End(xlUp))
End With

For Each i In RngColF
i.Value = 0
If Cells(i.Row, 3).Interior.ColorIndex = 10 Or Cells(i.Row,
4).Interior.ColorIndex = 10 Then
Cells(i.Row, 6) = 1
End If
Next
End Sub