Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected Rows?
The following macro toggles between shading the selected row(s) gray or, if
they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected Rows?
try
cell(Activecell.row,34) = "Completed" "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected Rows?
In keeping with your thoughts about people inserting/deleting columns and the
idea of naming the column: Just give a cell in that column in a row you have reasonable certainty won't be totally deleted - say in row 1 where you've probably got headers/titles anyhow - the name. For testing, I named AH1 as CompletedColumn. Now that can be referred to in your code in this fashion: Cells(ActiveCell.Row, Range("CompletedColumn").Column) = "Completed" ActiveCell.Row will give you proper row number (or at least top row if you have several rows selected), and Range("CompletedColumn").Column will always give the correct column number regardless of changes users make [until they wipe out the row with the named range in it, or the column itself]. "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected R
BJ and JLathem: Thanks both for your replies! Both of them work well, but
since the latter solution gives me a bit more flexibility, I'll probably go with that. JLathem: In your response you brought up something I hadn't thought of: The macro DOES only input the phrase "Completed" into the first cell in the column in question if more than one row is selected. Do you have any ideas about getting ALL of the cells in that column (in a multi-row selection) to return "Completed"--not just the top one? Again, I appreciate the advice from both of you! You've made my day a little bit better, which I needed. (But then, who doesn't?) "JLatham" wrote: In keeping with your thoughts about people inserting/deleting columns and the idea of naming the column: Just give a cell in that column in a row you have reasonable certainty won't be totally deleted - say in row 1 where you've probably got headers/titles anyhow - the name. For testing, I named AH1 as CompletedColumn. Now that can be referred to in your code in this fashion: Cells(ActiveCell.Row, Range("CompletedColumn").Column) = "Completed" ActiveCell.Row will give you proper row number (or at least top row if you have several rows selected), and Range("CompletedColumn").Column will always give the correct column number regardless of changes users make [until they wipe out the row with the named range in it, or the column itself]. "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected R
BJ: Thanks for your help! See my reply to JLatham, below.
"bj" wrote: try cell(Activecell.row,34) = "Completed" "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected R
How about:
Dim anyRow As Object For Each anyRow In Selection.Rows Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed" Next "PBJ" wrote: BJ and JLathem: Thanks both for your replies! Both of them work well, but since the latter solution gives me a bit more flexibility, I'll probably go with that. JLathem: In your response you brought up something I hadn't thought of: The macro DOES only input the phrase "Completed" into the first cell in the column in question if more than one row is selected. Do you have any ideas about getting ALL of the cells in that column (in a multi-row selection) to return "Completed"--not just the top one? Again, I appreciate the advice from both of you! You've made my day a little bit better, which I needed. (But then, who doesn't?) "JLatham" wrote: In keeping with your thoughts about people inserting/deleting columns and the idea of naming the column: Just give a cell in that column in a row you have reasonable certainty won't be totally deleted - say in row 1 where you've probably got headers/titles anyhow - the name. For testing, I named AH1 as CompletedColumn. Now that can be referred to in your code in this fashion: Cells(ActiveCell.Row, Range("CompletedColumn").Column) = "Completed" ActiveCell.Row will give you proper row number (or at least top row if you have several rows selected), and Range("CompletedColumn").Column will always give the correct column number regardless of changes users make [until they wipe out the row with the named range in it, or the column itself]. "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected R
Thank you so much! It works wonderfully. What a great way to start the
day--and probably your generous assistance will have been the high point! Many, many thanks! "JLatham" wrote: How about: Dim anyRow As Object For Each anyRow In Selection.Rows Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed" Next "PBJ" wrote: BJ and JLathem: Thanks both for your replies! Both of them work well, but since the latter solution gives me a bit more flexibility, I'll probably go with that. JLathem: In your response you brought up something I hadn't thought of: The macro DOES only input the phrase "Completed" into the first cell in the column in question if more than one row is selected. Do you have any ideas about getting ALL of the cells in that column (in a multi-row selection) to return "Completed"--not just the top one? Again, I appreciate the advice from both of you! You've made my day a little bit better, which I needed. (But then, who doesn't?) "JLatham" wrote: In keeping with your thoughts about people inserting/deleting columns and the idea of naming the column: Just give a cell in that column in a row you have reasonable certainty won't be totally deleted - say in row 1 where you've probably got headers/titles anyhow - the name. For testing, I named AH1 as CompletedColumn. Now that can be referred to in your code in this fashion: Cells(ActiveCell.Row, Range("CompletedColumn").Column) = "Completed" ActiveCell.Row will give you proper row number (or at least top row if you have several rows selected), and Range("CompletedColumn").Column will always give the correct column number regardless of changes users make [until they wipe out the row with the named range in it, or the column itself]. "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA, How to Refer to Cell In Specific Column But Selected R
"...your generous assistance will have been the high point!" For your sake,
I hope not <g You're welcome, glad to have been able to assist. "PBJ" wrote: Thank you so much! It works wonderfully. What a great way to start the day--and probably your generous assistance will have been the high point! Many, many thanks! "JLatham" wrote: How about: Dim anyRow As Object For Each anyRow In Selection.Rows Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed" Next "PBJ" wrote: BJ and JLathem: Thanks both for your replies! Both of them work well, but since the latter solution gives me a bit more flexibility, I'll probably go with that. JLathem: In your response you brought up something I hadn't thought of: The macro DOES only input the phrase "Completed" into the first cell in the column in question if more than one row is selected. Do you have any ideas about getting ALL of the cells in that column (in a multi-row selection) to return "Completed"--not just the top one? Again, I appreciate the advice from both of you! You've made my day a little bit better, which I needed. (But then, who doesn't?) "JLatham" wrote: In keeping with your thoughts about people inserting/deleting columns and the idea of naming the column: Just give a cell in that column in a row you have reasonable certainty won't be totally deleted - say in row 1 where you've probably got headers/titles anyhow - the name. For testing, I named AH1 as CompletedColumn. Now that can be referred to in your code in this fashion: Cells(ActiveCell.Row, Range("CompletedColumn").Column) = "Completed" ActiveCell.Row will give you proper row number (or at least top row if you have several rows selected), and Range("CompletedColumn").Column will always give the correct column number regardless of changes users make [until they wipe out the row with the named range in it, or the column itself]. "PBJ" wrote: The following macro toggles between shading the selected row(s) gray or, if they're shaded already, unshading them. (It opens a form to resolve the issue if rows of more than one shade are selected.) Sub ShadeRow() Select Case Selection.Interior.ColorIndex Case Is < 16 Selection.EntireRow.Interior.ColorIndex = 16 Case Is = 16 Selection.EntireRow.Interior.ColorIndex = xlNone Case Else ShadingOptions.Show End Select End Sub It works pretty well. Probably this is a stupid question, but I'd really like the macro to also insert the text "Completed" into the cell in column AH (that is, 34) for whatever row(s) are being shaded. I've tried variations on the Cells property, but I always end up inputing "Completed" into a cell that is relative to the active cell, which isn't what I want. (Further, since it's possible that columns may be added or deleted by users, I suppose that I really should be referring to a vertical named range instead of the actual column number.) I'd appreciate any advice on my stupid little problem! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Specific Cell to Selected Cell | Excel Worksheet Functions | |||
Printing only Rows with a value in a specific column | Excel Discussion (Misc queries) | |||
Function to return # of column with min value in selected rows | Excel Worksheet Functions | |||
Specific cell selected when sheet opens...how??? | Excel Discussion (Misc queries) | |||
Using 'If' refer to specific words in a cell containing text | Excel Worksheet Functions |