Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
Hi, all:
Not know why my posts are not shown up. Another same post: I manually highlight a cell or cells with green color. Whenever I do so, I want to return a value 1 in another cell in the same row. Otherwise, that value will be 0. How to achive this? Thanks, Jorge |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
if you're using google groups, yesterday & today it's not updating.
:( it's slowly catching up..... right now it's 1:32 pm & i'm showing the latest posts are 11am. which is better than it was yesterday. (reading this via OE) susan wrote in message ups.com... Hi, all: Not know why my posts are not shown up. Another same post: I manually highlight a cell or cells with green color. Whenever I do so, I want to return a value 1 in another cell in the same row. Otherwise, that value will be 0. How to achive this? Thanks, Jorge |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
This is a sample for row 7. The value goes in A7. The color tested for is
green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
On May 17, 12:44 pm, Gary''s Student
wrote: This is a sample for row 7. The value goes in A7. The color tested for is green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 Susan and Gary: Thank you so much for your input! Maybe my question is not clear. Please allow me to re-instate it. 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 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. Any thoughts? Thanks, Jorge |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
On May 17, 1:52 pm, "
wrote: On May 17, 12:44 pm, Gary''s Student wrote: This is a sample for row 7. The value goes in A7. The color tested for is green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 Susan and Gary: Thank you so much for your input! Maybe my question is not clear. Please allow me to re-instate it. 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 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. Any thoughts? Thanks, Jorge- Hide quoted text - - Show quoted text - Hi, Gary: I modified your code to the following. Apparently, it is not working. Please advise! Thanks, 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
Are you sure the shade of green you are using is ColorIndex 10? Or is it
4, 14 or 43? You have to match the number to the color you use. When you select the color from the palette, count the number of color swatches from left to right and down. " wrote: On May 17, 1:52 pm, " wrote: On May 17, 12:44 pm, Gary''s Student wrote: This is a sample for row 7. The value goes in A7. The color tested for is green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 Susan and Gary: Thank you so much for your input! Maybe my question is not clear. Please allow me to re-instate it. 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 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. Any thoughts? Thanks, Jorge- Hide quoted text - - Show quoted text - Hi, Gary: I modified your code to the following. Apparently, it is not working. Please advise! Thanks, 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
On May 17, 6:31 pm, JLGWhiz wrote:
Are you sure the shade of green you are using is ColorIndex 10? Or is it 4, 14 or 43? You have to match the number to the color you use. When you select the color from the palette, count the number of color swatches from left to right and down. " wrote: On May 17, 1:52 pm, " wrote: On May 17, 12:44 pm, Gary''s Student wrote: This is a sample for row 7. The value goes in A7. The color tested for is green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 Susan and Gary: Thank you so much for your input! Maybe my question is not clear. Please allow me to re-instate it. 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 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. Any thoughts? Thanks, Jorge- Hide quoted text - - Show quoted text - Hi, Gary: I modified your code to the following. Apparently, it is not working. Please advise! Thanks, 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 - Yes, I am sure the color index is 10. thanks, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-post
On May 17, 7:16 pm, "
wrote: On May 17, 6:31 pm, JLGWhiz wrote: Are you sure the shade of green you are using is ColorIndex 10? Or is it 4, 14 or 43? You have to match the number to the color you use. When you select the color from the palette, count the number of color swatches from left to right and down. " wrote: On May 17, 1:52 pm, " wrote: On May 17, 12:44 pm, Gary''s Student wrote: This is a sample for row 7. The value goes in A7. The color tested for is green #10: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A7").Value = 0 For i = 1 To Columns.Count If Cells(7, i).Interior.ColorIndex = 10 Then Range("A7").Value = 1 Exit Sub End If Next End Sub This goes in the Worksheet code area not a standard module. Once you have colored a cell, move away from it to tell the routine you are done. -- Gary''s Student - gsnu200722 Susan and Gary: Thank you so much for your input! Maybe my question is not clear. Please allow me to re-instate it. 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 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. Any thoughts? Thanks, Jorge- Hide quoted text - - Show quoted text - Hi, Gary: I modified your code to the following. Apparently, it is not working. Please advise! Thanks, 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 - Yes, I am sure the color index is 10. thanks,- Hide quoted text - - Show quoted text - Hi, Anybody has any ideas? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Before my first post | About this forum | |||
my post | Excel Discussion (Misc queries) | |||
will this post | Excel Discussion (Misc queries) | |||
Should I generally request "post a poll" when I post a new thread? | Excel Discussion (Misc queries) | |||
Re-Post Anyone know how to do this? | Excel Programming |