VBA Color Operations

[rlderi01]

All,

I have manually-formatted cells colored colorindex =6, spread randomly throughout the sheet. I need to write a macro to select these colored cells, and use the offset function to move them (2, -1). Any help greatly appreciated!

RD

 

[rlderi01]

Additional info- the destination cells will be blank, and nothing else will have to be moved other than the target yellow cells

 

[sous2817]

so you want to move the data in the yellow color cell, not to color? Also, do you want the source cell cleared out or leave the data in?

 

[sulakvea]

hi, and welcome to MrExcel

smth like this maybe?

Sub test()
On Error Resume Next
For Each TmpRng In UsedRange
 If TmpRng.Interior.ColorIndex = 6 Then
  TmpRng.Interior.ColorIndex = 0
  TmpRng.Offset(2, -1).Interior.ColorIndex = 6
 End If
Next
End Sub

P.S. edition: just noticed Sous's question - i was under the impression you needed to move the coloring, not the cell value. my code moves the yellow color by offset(2,-1), not the content, of the cells.

 

[sous2817]

Here's my stab at it:

Sub foo()
Dim rng As Range, c As Range

Application.ScreenUpdating = False
Set rng = ActiveSheet.UsedRange
For Each c In rng
    If c.Interior.Color = 65535 Then
        c.Offset(2, -1).Value = c.Value
        c.Value = ""
        c.Interior.Pattern = xlNone
    End If
Next c

Application.ScreenUpdating = True
End Sub

 

[rlderi01]

Yes, only the data gets transferred, and the source gets cleared

 

[sous2817]

See my edited post above and let me know if that's close to what you're expecting.

Also, welcome to the board!

 

[sulakvea]

ok, then i wont duplicate what Sous already wrote - go with his suggestion

 

[rlderi01]

sous2817- that works, only I need the source cell cleared and as of now it just copies the cell leaving the source populated. Thank you though that's already a lot more than I was able to do!

 

[sous2817]

I edited my post to clear both the source text and source color. Recopy it and give it another whirl and let me know if it works.