Need help with coding that would look at cells within columns that are
colored (shaded) and automatically copy/paste to the next column over and chg
the links to the next row # in the link

For example:
Column Z;Row 4 is a shaded cell, that has the link formula referencing
another files, "Sheet1!$D$60".

The coding I want would look at that link formula and copy/paste to Column
AA; Row 4 but now show the link reference as "Sheet1!$D$61"

I know this sounds and looks crazy, but it's really what I need. For the
code to look for the shaded cell, and increase the row by 1 after it
copies/paste the formula in the next column.

ANY and all guidance is greatly appreciated! )
--
JCarter
Still Learning

Here is a macro that does what you wanted I think. You must start the macro
with the active cell being at the bottom row of your data. It then will work
its way up performing the operation on all rows except row one, which I
assume has a label. I'm also assuming that you want this performed on any
shaded row, regardless of the color, etc.

I tested this and it worked for me. Please test it on a saved file so that
if you don't get the results you wanted, you can just close and not save and
then re-open to get back to where you were before you ran the macro, because
you can't use the undo operation to undo a macro.

Sub Shading_Test()
Dim MyRow
Do While ActiveCell.Row > 1
If ActiveCell.Interior.ColorIndex <> xlNone Then
MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
MyRow = MyRow + 1
ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
End If
ActiveCell.Offset(-1, 0).Select
Loop
End Sub

Hope this helps.

Keith

"JCarter" wrote:

> Need help with coding that would look at cells within columns that are
> colored (shaded) and automatically copy/paste to the next column over and chg
> the links to the next row # in the link
>
> For example:
> Column Z;Row 4 is a shaded cell, that has the link formula referencing
> another files, "Sheet1!$D$60".
>
> The coding I want would look at that link formula and copy/paste to Column
> AA; Row 4 but now show the link reference as "Sheet1!$D$61"
>
> I know this sounds and looks crazy, but it's really what I need. For the
> code to look for the shaded cell, and increase the row by 1 after it
> copies/paste the formula in the next column.
>
> ANY and all guidance is greatly appreciated! )
> --
> JCarter
> Still Learning

Keith, thank you so much for taking the time to help me! I ran the macro but
rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
looking at the "MyRow = MyRow +1"

How would I correct this?

--
JCarter
Still Learning


"Keithlo" wrote:

> Here is a macro that does what you wanted I think. You must start the macro
> with the active cell being at the bottom row of your data. It then will work
> its way up performing the operation on all rows except row one, which I
> assume has a label. I'm also assuming that you want this performed on any
> shaded row, regardless of the color, etc.
>
> I tested this and it worked for me. Please test it on a saved file so that
> if you don't get the results you wanted, you can just close and not save and
> then re-open to get back to where you were before you ran the macro, because
> you can't use the undo operation to undo a macro.
>
> Sub Shading_Test()
> Dim MyRow
> Do While ActiveCell.Row > 1
> If ActiveCell.Interior.ColorIndex <> xlNone Then
> MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> MyRow = MyRow + 1
> ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> End If
> ActiveCell.Offset(-1, 0).Select
> Loop
> End Sub
>
> Hope this helps.
>
> Keith
>
> "JCarter" wrote:
>
> > Need help with coding that would look at cells within columns that are
> > colored (shaded) and automatically copy/paste to the next column over and chg
> > the links to the next row # in the link
> >
> > For example:
> > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > another files, "Sheet1!$D$60".
> >
> > The coding I want would look at that link formula and copy/paste to Column
> > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> >
> > I know this sounds and looks crazy, but it's really what I need. For the
> > code to look for the shaded cell, and increase the row by 1 after it
> > copies/paste the formula in the next column.
> >
> > ANY and all guidance is greatly appreciated! )
> > --
> > JCarter
> > Still Learning

When you get the error, click on Debug, and hover your cursor over MyRow in
the code, and let me know what value is shown for it. That will help me
figure out what the problem is.

Keith



"JCarter" wrote:

> Keith, thank you so much for taking the time to help me! I ran the macro but
> rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
> looking at the "MyRow = MyRow +1"
>
> How would I correct this?
>
> --
> JCarter
> Still Learning
>
>
> "Keithlo" wrote:
>
> > Here is a macro that does what you wanted I think. You must start the macro
> > with the active cell being at the bottom row of your data. It then will work
> > its way up performing the operation on all rows except row one, which I
> > assume has a label. I'm also assuming that you want this performed on any
> > shaded row, regardless of the color, etc.
> >
> > I tested this and it worked for me. Please test it on a saved file so that
> > if you don't get the results you wanted, you can just close and not save and
> > then re-open to get back to where you were before you ran the macro, because
> > you can't use the undo operation to undo a macro.
> >
> > Sub Shading_Test()
> > Dim MyRow
> > Do While ActiveCell.Row > 1
> > If ActiveCell.Interior.ColorIndex <> xlNone Then
> > MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> > MyRow = MyRow + 1
> > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> > End If
> > ActiveCell.Offset(-1, 0).Select
> > Loop
> > End Sub
> >
> > Hope this helps.
> >
> > Keith
> >
> > "JCarter" wrote:
> >
> > > Need help with coding that would look at cells within columns that are
> > > colored (shaded) and automatically copy/paste to the next column over and chg
> > > the links to the next row # in the link
> > >
> > > For example:
> > > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > > another files, "Sheet1!$D$60".
> > >
> > > The coding I want would look at that link formula and copy/paste to Column
> > > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> > >
> > > I know this sounds and looks crazy, but it's really what I need. For the
> > > code to look for the shaded cell, and increase the row by 1 after it
> > > copies/paste the formula in the next column.
> > >
> > > ANY and all guidance is greatly appreciated! )
> > > --
> > > JCarter
> > > Still Learning

it show "MyRow = (and then it's shows the link to my feeder file)

All my shaded cells are links to other files/worksheets outside this
particular file.
--
JCarter
Still Learning


"Keithlo" wrote:

> When you get the error, click on Debug, and hover your cursor over MyRow in
> the code, and let me know what value is shown for it. That will help me
> figure out what the problem is.
>
> Keith
>
>
>
> "JCarter" wrote:
>
> > Keith, thank you so much for taking the time to help me! I ran the macro but
> > rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
> > looking at the "MyRow = MyRow +1"
> >
> > How would I correct this?
> >
> > --
> > JCarter
> > Still Learning
> >
> >
> > "Keithlo" wrote:
> >
> > > Here is a macro that does what you wanted I think. You must start the macro
> > > with the active cell being at the bottom row of your data. It then will work
> > > its way up performing the operation on all rows except row one, which I
> > > assume has a label. I'm also assuming that you want this performed on any
> > > shaded row, regardless of the color, etc.
> > >
> > > I tested this and it worked for me. Please test it on a saved file so that
> > > if you don't get the results you wanted, you can just close and not save and
> > > then re-open to get back to where you were before you ran the macro, because
> > > you can't use the undo operation to undo a macro.
> > >
> > > Sub Shading_Test()
> > > Dim MyRow
> > > Do While ActiveCell.Row > 1
> > > If ActiveCell.Interior.ColorIndex <> xlNone Then
> > > MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> > > MyRow = MyRow + 1
> > > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> > > End If
> > > ActiveCell.Offset(-1, 0).Select
> > > Loop
> > > End Sub
> > >
> > > Hope this helps.
> > >
> > > Keith
> > >
> > > "JCarter" wrote:
> > >
> > > > Need help with coding that would look at cells within columns that are
> > > > colored (shaded) and automatically copy/paste to the next column over and chg
> > > > the links to the next row # in the link
> > > >
> > > > For example:
> > > > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > > > another files, "Sheet1!$D$60".
> > > >
> > > > The coding I want would look at that link formula and copy/paste to Column
> > > > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> > > >
> > > > I know this sounds and looks crazy, but it's really what I need. For the
> > > > code to look for the shaded cell, and increase the row by 1 after it
> > > > copies/paste the formula in the next column.
> > > >
> > > > ANY and all guidance is greatly appreciated! )
> > > > --
> > > > JCarter
> > > > Still Learning

I'm sorry, my code was weak. It used absolute values to determine the row
portion. This new code figures out the row number based on the dollar sign.
There is still one weakness. If your formulas have no $ signs in them it
will fail. But since they are all referencing another workbook, they would
have dollar signs unless they were removed after creating the reference.
There's probably a better way to do this, but it will work if the dollar
signs are there. I'm assuming all your shaded cells have a formula
referencing a different workbook. Try this and see how it works.

Sub Shading_Test()
Dim MyRow, MyChar, MyPlace
Do While ActiveCell.Row > 1
If ActiveCell.Interior.ColorIndex <> xlNone Then

MyPlace = Len(ActiveCell.Formula)
MyChar = Mid(ActiveCell.Formula, MyPlace, 1)

Do While MyPlace > 1
If MyChar = "$" Then
MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) -
MyPlace)
Exit Do
Else
MyPlace = MyPlace - 1
MyChar = Mid(ActiveCell.Formula, MyPlace, 1)
End If
Loop

MyRow = MyRow + 1
ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow
End If
ActiveCell.Offset(-1, 0).Select
Loop
End Sub

Keith

"JCarter" wrote:

> it show "MyRow = (and then it's shows the link to my feeder file)
>
> All my shaded cells are links to other files/worksheets outside this
> particular file.
> --
> JCarter
> Still Learning
>
>
> "Keithlo" wrote:
>
> > When you get the error, click on Debug, and hover your cursor over MyRow in
> > the code, and let me know what value is shown for it. That will help me
> > figure out what the problem is.
> >
> > Keith
> >
> >
> >
> > "JCarter" wrote:
> >
> > > Keith, thank you so much for taking the time to help me! I ran the macro but
> > > rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
> > > looking at the "MyRow = MyRow +1"
> > >
> > > How would I correct this?
> > >
> > > --
> > > JCarter
> > > Still Learning
> > >
> > >
> > > "Keithlo" wrote:
> > >
> > > > Here is a macro that does what you wanted I think. You must start the macro
> > > > with the active cell being at the bottom row of your data. It then will work
> > > > its way up performing the operation on all rows except row one, which I
> > > > assume has a label. I'm also assuming that you want this performed on any
> > > > shaded row, regardless of the color, etc.
> > > >
> > > > I tested this and it worked for me. Please test it on a saved file so that
> > > > if you don't get the results you wanted, you can just close and not save and
> > > > then re-open to get back to where you were before you ran the macro, because
> > > > you can't use the undo operation to undo a macro.
> > > >
> > > > Sub Shading_Test()
> > > > Dim MyRow
> > > > Do While ActiveCell.Row > 1
> > > > If ActiveCell.Interior.ColorIndex <> xlNone Then
> > > > MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> > > > MyRow = MyRow + 1
> > > > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> > > > End If
> > > > ActiveCell.Offset(-1, 0).Select
> > > > Loop
> > > > End Sub
> > > >
> > > > Hope this helps.
> > > >
> > > > Keith
> > > >
> > > > "JCarter" wrote:
> > > >
> > > > > Need help with coding that would look at cells within columns that are
> > > > > colored (shaded) and automatically copy/paste to the next column over and chg
> > > > > the links to the next row # in the link
> > > > >
> > > > > For example:
> > > > > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > > > > another files, "Sheet1!$D$60".
> > > > >
> > > > > The coding I want would look at that link formula and copy/paste to Column
> > > > > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> > > > >
> > > > > I know this sounds and looks crazy, but it's really what I need. For the
> > > > > code to look for the shaded cell, and increase the row by 1 after it
> > > > > copies/paste the formula in the next column.
> > > > >
> > > > > ANY and all guidance is greatly appreciated! )
> > > > > --
> > > > > JCarter
> > > > > Still Learning

You are a GENIUS!!!!!!! It works like a charm! Thank you thank you thank
you!!!!

--
JCarter
ALWAYS Learning


"Keithlo" wrote:

> I'm sorry, my code was weak. It used absolute values to determine the row
> portion. This new code figures out the row number based on the dollar sign.
> There is still one weakness. If your formulas have no $ signs in them it
> will fail. But since they are all referencing another workbook, they would
> have dollar signs unless they were removed after creating the reference.
> There's probably a better way to do this, but it will work if the dollar
> signs are there. I'm assuming all your shaded cells have a formula
> referencing a different workbook. Try this and see how it works.
>
> Sub Shading_Test()
> Dim MyRow, MyChar, MyPlace
> Do While ActiveCell.Row > 1
> If ActiveCell.Interior.ColorIndex <> xlNone Then
>
> MyPlace = Len(ActiveCell.Formula)
> MyChar = Mid(ActiveCell.Formula, MyPlace, 1)
>
> Do While MyPlace > 1
> If MyChar = "$" Then
> MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) -
> MyPlace)
> Exit Do
> Else
> MyPlace = MyPlace - 1
> MyChar = Mid(ActiveCell.Formula, MyPlace, 1)
> End If
> Loop
>
> MyRow = MyRow + 1
> ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow
> End If
> ActiveCell.Offset(-1, 0).Select
> Loop
> End Sub
>
> Keith
>
> "JCarter" wrote:
>
> > it show "MyRow = (and then it's shows the link to my feeder file)
> >
> > All my shaded cells are links to other files/worksheets outside this
> > particular file.
> > --
> > JCarter
> > Still Learning
> >
> >
> > "Keithlo" wrote:
> >
> > > When you get the error, click on Debug, and hover your cursor over MyRow in
> > > the code, and let me know what value is shown for it. That will help me
> > > figure out what the problem is.
> > >
> > > Keith
> > >
> > >
> > >
> > > "JCarter" wrote:
> > >
> > > > Keith, thank you so much for taking the time to help me! I ran the macro but
> > > > rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
> > > > looking at the "MyRow = MyRow +1"
> > > >
> > > > How would I correct this?
> > > >
> > > > --
> > > > JCarter
> > > > Still Learning
> > > >
> > > >
> > > > "Keithlo" wrote:
> > > >
> > > > > Here is a macro that does what you wanted I think. You must start the macro
> > > > > with the active cell being at the bottom row of your data. It then will work
> > > > > its way up performing the operation on all rows except row one, which I
> > > > > assume has a label. I'm also assuming that you want this performed on any
> > > > > shaded row, regardless of the color, etc.
> > > > >
> > > > > I tested this and it worked for me. Please test it on a saved file so that
> > > > > if you don't get the results you wanted, you can just close and not save and
> > > > > then re-open to get back to where you were before you ran the macro, because
> > > > > you can't use the undo operation to undo a macro.
> > > > >
> > > > > Sub Shading_Test()
> > > > > Dim MyRow
> > > > > Do While ActiveCell.Row > 1
> > > > > If ActiveCell.Interior.ColorIndex <> xlNone Then
> > > > > MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> > > > > MyRow = MyRow + 1
> > > > > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> > > > > End If
> > > > > ActiveCell.Offset(-1, 0).Select
> > > > > Loop
> > > > > End Sub
> > > > >
> > > > > Hope this helps.
> > > > >
> > > > > Keith
> > > > >
> > > > > "JCarter" wrote:
> > > > >
> > > > > > Need help with coding that would look at cells within columns that are
> > > > > > colored (shaded) and automatically copy/paste to the next column over and chg
> > > > > > the links to the next row # in the link
> > > > > >
> > > > > > For example:
> > > > > > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > > > > > another files, "Sheet1!$D$60".
> > > > > >
> > > > > > The coding I want would look at that link formula and copy/paste to Column
> > > > > > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> > > > > >
> > > > > > I know this sounds and looks crazy, but it's really what I need. For the
> > > > > > code to look for the shaded cell, and increase the row by 1 after it
> > > > > > copies/paste the formula in the next column.
> > > > > >
> > > > > > ANY and all guidance is greatly appreciated! )
> > > > > > --
> > > > > > JCarter
> > > > > > Still Learning

You're welcome! I'm glad it worked.

I did figure out a better way to determine the row of a formula by using the
FormulaR1C1 property of the active cell, but it would take some time to code.
So since this worked for you, I'll just tuck that away for future reference.

Keith

"JCarter" wrote:

> You are a GENIUS!!!!!!! It works like a charm! Thank you thank you thank
> you!!!!
>
> --
> JCarter
> ALWAYS Learning
>
>
> "Keithlo" wrote:
>
> > I'm sorry, my code was weak. It used absolute values to determine the row
> > portion. This new code figures out the row number based on the dollar sign.
> > There is still one weakness. If your formulas have no $ signs in them it
> > will fail. But since they are all referencing another workbook, they would
> > have dollar signs unless they were removed after creating the reference.
> > There's probably a better way to do this, but it will work if the dollar
> > signs are there. I'm assuming all your shaded cells have a formula
> > referencing a different workbook. Try this and see how it works.
> >
> > Sub Shading_Test()
> > Dim MyRow, MyChar, MyPlace
> > Do While ActiveCell.Row > 1
> > If ActiveCell.Interior.ColorIndex <> xlNone Then
> >
> > MyPlace = Len(ActiveCell.Formula)
> > MyChar = Mid(ActiveCell.Formula, MyPlace, 1)
> >
> > Do While MyPlace > 1
> > If MyChar = "$" Then
> > MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) -
> > MyPlace)
> > Exit Do
> > Else
> > MyPlace = MyPlace - 1
> > MyChar = Mid(ActiveCell.Formula, MyPlace, 1)
> > End If
> > Loop
> >
> > MyRow = MyRow + 1
> > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow
> > End If
> > ActiveCell.Offset(-1, 0).Select
> > Loop
> > End Sub
> >
> > Keith
> >
> > "JCarter" wrote:
> >
> > > it show "MyRow = (and then it's shows the link to my feeder file)
> > >
> > > All my shaded cells are links to other files/worksheets outside this
> > > particular file.
> > > --
> > > JCarter
> > > Still Learning
> > >
> > >
> > > "Keithlo" wrote:
> > >
> > > > When you get the error, click on Debug, and hover your cursor over MyRow in
> > > > the code, and let me know what value is shown for it. That will help me
> > > > figure out what the problem is.
> > > >
> > > > Keith
> > > >
> > > >
> > > >
> > > > "JCarter" wrote:
> > > >
> > > > > Keith, thank you so much for taking the time to help me! I ran the macro but
> > > > > rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be
> > > > > looking at the "MyRow = MyRow +1"
> > > > >
> > > > > How would I correct this?
> > > > >
> > > > > --
> > > > > JCarter
> > > > > Still Learning
> > > > >
> > > > >
> > > > > "Keithlo" wrote:
> > > > >
> > > > > > Here is a macro that does what you wanted I think. You must start the macro
> > > > > > with the active cell being at the bottom row of your data. It then will work
> > > > > > its way up performing the operation on all rows except row one, which I
> > > > > > assume has a label. I'm also assuming that you want this performed on any
> > > > > > shaded row, regardless of the color, etc.
> > > > > >
> > > > > > I tested this and it worked for me. Please test it on a saved file so that
> > > > > > if you don't get the results you wanted, you can just close and not save and
> > > > > > then re-open to get back to where you were before you ran the macro, because
> > > > > > you can't use the undo operation to undo a macro.
> > > > > >
> > > > > > Sub Shading_Test()
> > > > > > Dim MyRow
> > > > > > Do While ActiveCell.Row > 1
> > > > > > If ActiveCell.Interior.ColorIndex <> xlNone Then
> > > > > > MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11)
> > > > > > MyRow = MyRow + 1
> > > > > > ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow
> > > > > > End If
> > > > > > ActiveCell.Offset(-1, 0).Select
> > > > > > Loop
> > > > > > End Sub
> > > > > >
> > > > > > Hope this helps.
> > > > > >
> > > > > > Keith
> > > > > >
> > > > > > "JCarter" wrote:
> > > > > >
> > > > > > > Need help with coding that would look at cells within columns that are
> > > > > > > colored (shaded) and automatically copy/paste to the next column over and chg
> > > > > > > the links to the next row # in the link
> > > > > > >
> > > > > > > For example:
> > > > > > > Column Z;Row 4 is a shaded cell, that has the link formula referencing
> > > > > > > another files, "Sheet1!$D$60".
> > > > > > >
> > > > > > > The coding I want would look at that link formula and copy/paste to Column
> > > > > > > AA; Row 4 but now show the link reference as "Sheet1!$D$61"
> > > > > > >
> > > > > > > I know this sounds and looks crazy, but it's really what I need. For the
> > > > > > > code to look for the shaded cell, and increase the row by 1 after it
> > > > > > > copies/paste the formula in the next column.
> > > > > > >
> > > > > > > ANY and all guidance is greatly appreciated! )
> > > > > > > --
> > > > > > > JCarter
> > > > > > > Still Learning