Conditional Formatting in cells

[dshafique]

Hi Guys, having an issue where I cant copy and paste cells that have conditional formatting to different worksheets. I have it where a cell will be green if above a reference value, and red if it falls below, but turns white when i paste it into a different worksheet. and i get an error about copying formatted cells.

 

[dshafique]

thanks for the code, so is there a way to use this where it selects the cell by table references instead of having the user input? its a dynamic sheet where data changes per month. so the code i used to select it dynamically is

Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.offset(1,1).select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select

the header on the right of Fiscal YTD is some month which is new each month. so the idea is that it selects the cells below the column to the right, all the way to the end of the table and formats them.

is there a way to mix the two codes together?

(sorry im new to vba, as you can probably tell)

 

[MrKowz]

Sorry for the delayed response - I had to do some research on how to handle this.

Give this a shot:

Public Sub CopyPaste()
Dim srng        As Range, _
    drng        As Range, _
    rrng        As Range

Range("TableAll[[#Headers],[Fiscal YTD]]").Offset(1, 1).Select
Set srng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
Set drng = Application.InputBox("Please select the cell you want to paste the range into", Type:=8)

For Each rrng In srng
    With drng.Offset(rrng.Row - srng(1).Row, rrng.Column - srng(1).Column)
        .Value = rrng.Value
        .Interior.ColorIndex = rrng.DisplayFormat.Interior.ColorIndex
    End With
Next rrng

End Sub

 

[dshafique]

Thanks MrKowz, sorry for the late response, it was the weekend, so i didnt get a chance to try it out. Everything works great! I changed one line so it could choose it dynamically

Dim srng        As Range, _
    drng        As Range, _
    rrng        As Range


Range("TableAll[[#Headers],[Fiscal YTD]]").Offset(1, 1).Select
Set srng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
Set drng = Range("TableAll[[#Headers],[Fiscal YTD]]").Offset(1, 1)




For Each rrng In srng
    With drng.Offset(rrng.Row - srng(1).Row, rrng.Column - srng(1).Column)
        .Value = rrng.Value
        .Interior.ColorIndex = rrng.DisplayFormat.Interior.ColorIndex
    End With
Next rrng

when i copy and paste it to a different worksheet, the green turns to teal, and the red turns bright.

so the greens go from 0, 176, 80 (RGB) to 0, 128, 128.

and the reds go from 192, 0, 0 to 255, 0, 0 with black text instead of white text.

i dont care about the text color, but do you know why it would change the colors like that?