VBA to Cut/Paste Values Based on Formatting

[MIexcelNovice]

Hi Everyone,

To be honest I'm not even sure if this is something that can be done with VBA, but since I consider myself a beginner when it comes to VBA code I figured this would be the place to ask.

So, I have a worksheet full of data that has dollar amounts in column B and names in Column I. Some of the names in column I are formatted with different colors and some have no color. I was wondering if there is a way to identify the names that are colored in column I and if they are colored, I need the corresponding dollar amounts cut from column B and pasted into the same row in column K.

Once again, I don't even know if this is a possibility, so any information would be greatly appreciated.

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rName As Range
    For Each rName In Range("I2:I" & LastRow)
        If rName.Font.ColorIndex <> 1 Then
            Cells(rName.Row, "K") = Cells(rName.Row, "B")
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[MIexcelNovice]

Hi mumps,

Thanks for the reply. That code did move the data from column B (I actually misspoke in my original post, the dollar amounts are in column F) into column K. However, it moved all of the data, not just the data that had color formatting in column I. Also, it copied the data instead of cutting it, but that's probably something I could fix once I figure out how to get it to only move the data from rows that have color formatting in column I.

 

[mumps]

When I tried it on a dummy sheet, it worked properly. Is it the names (font) that are colored or is it the cell that is filled with a color?

 

[MIexcelNovice]

It is the cell that is filled with color, I probably should have made that more clear in the original post

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rName As Range
    For Each rName In Range("I2:I" & LastRow)
        If rName.Interior.ColorIndex <> xlNone And Cells(rName.Row, "F") <> "" Then
            Cells(rName.Row, "F").Cut Cells(rName.Row, "K")
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[MIexcelNovice]

That one didn't seem to move any of the data from column F

 

[mumps]

I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

 

[MIexcelNovice]

Alright, hopefully this link works:

https://app.box.com/s/ojyeypxklutg53aaii4p

 

[MIexcelNovice]

Hopefully this link works:

https://app.box.com/s/ojyeypxklutg53aaii4p

So the only dollar amounts that I need moved, are the ones that correspond with the colored cells in the 'RemitterName' column (column I). All of the remitter names that have no color formatting, the dollar amounts should stay in column F. However if the remitter name is colored, I would like the dollar amounts in column F, to cut/paste over to the same row in the empty column K