In VBA, How to Refer to Cell In Specific Column But Selected Rows?

[Guest]

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!

 

[Guest]

try
cell(Activecell.row,34) = "Completed"

 

[Guest]

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].

 

[Guest]

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?)

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].

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!

 

[Guest]

BJ: Thanks for your help! See my reply to JLatham, below.

 

[Guest]

How about:

Dim anyRow As Object
For Each anyRow In Selection.Rows
Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed"
Next

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?)

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].

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!

 

[Guest]

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!

How about:

Dim anyRow As Object
For Each anyRow In Selection.Rows
Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed"
Next

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?)

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].

:

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!

 

[Guest]

"...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.

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!

How about:

Dim anyRow As Object
For Each anyRow In Selection.Rows
Cells(anyRow.Row, Range("CompletedColumn").Column) = "Completed"
Next

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?)

:

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].

:

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!