VBA copy background color

[frankrss]

Hi, I want to copy the background color in column B to column A, but the column B uses conditional formatting to get the background color. How can I use macro to copy the color?
See attached file.

https://docs.google.com/spreadsheet/ccc?key=0AqhsfIOFA5jHdHNUek5xaERjeGhub3ROdlRnWkdocmc&hl=en_US

Thanks

 

[frankrss]

Sub copycolors()
    Dim cell As Range, c As Long
    For Each cell In Range("A2:A200")
    cell.Interior.Color = cell.Offset(0, 1).formatCondtions(1).Interior.Color
    Next cell
End Sub

It does not work.

 

[Trevor G]

The conditional formatting is the problem Frank,

So what I have done is look at the conditions you have set and then deleted them, then used some VBA code to set the Colour conditions and the other code would work.

If you copy your workbook then delete the Conditional Formatting and use this code. You might have to expand on it. If it works then you could then do something to place it in the worksheet code.

Sub col()
Dim ran As Range
For Each ran In Range("B2:B15")
If ran.Value > 0.001 Then
ran.Interior.Color = RGB(255, 0, 0)
ElseIf ran.Value < 2 Then
ran.Interior.Color = RGB(255, 204, 0)
ElseIf ran.Value > 1 Then
ran.Interior.Color = RGB(204, 255, 204)
Else
ran.Interior = vbNormal

End If
Next ran

End Sub

 

[gregtx81]

It does not work.

There's a missing "i" in formatConditions. However, it still may not work-- I think it depends on what specific type of conditional formatting is used. The following works for me, using a simple one color conditional format (highlight if above some value).

Sub copycolors()
    On Error Resume Next
    Dim cell As Range, c As Long
    For Each cell In Range("A4:A200")        
        cell.Interior.Color = cell.Offset(0, 1).FormatConditions(1).Interior.Color
    Next cell
End Sub

On Error Resume Next statement is for when there is no conditional format in the applicable cell in Column B.

 

[frankrss]

It works now, so great your guys, without functions you get this done.

Sub TEST()
Dim ran As Range
For Each ran In Range("B2:B50")
If ran.Value < Range("D" & ran.Row).Value Then
ran.Interior.ColorIndex = 3
ElseIf ran.Value = Leeg Then
ElseIf ran.Value <= Range("G" & ran.Row).Value Then
ran.Interior.ColorIndex = 44
ElseIf ran.Value > Range("H" & ran.Row).Value Then
ran.Interior.ColorIndex = 35
Else
End If
If Range("B" & ran.Row).Interior.Color = xlNone Then
Range("A" & ran.Row).Interior.Color = xlNone
Else
Range("A" & ran.Row).Interior.ColorIndex = ran.Interior.ColorIndex
End If
Next ran
End Sub