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.Interior.ColorIndex <> 2 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]

Wow, that worked perfectly. I can't thank you enough, you just saved me hours of work.

 

[MIexcelNovice]

Not sure what is happening. That code worked perfectly on the dummy worksheet, but when I tried it on another worksheet with the exact same format (just has different cells colored in the I column), it went back to moving all of the values from column F to column K. I think I should be able to work it out from here, I'm just not sure why it is doing this

 

[mumps]

If you post a link to the other worksheet, I can have a look.

 

[MIexcelNovice]

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

The cells in column I are conditionally formatted by name, but it didn't look like that mattered with the code that you wrote.

Your code is attached to the macro MoveAmounts which should be on that worksheet. I appreciate your help on this.

 

[mumps]

That's where the problem lies. I know of no way to make it work if the cell fill is applied using conditional formatting.

 

[MIexcelNovice]

Ah I see, well I appreciate you looking into it

 

[mumps]

Glad to help.