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.

 

[MrKowz]

Do the cells also contain formulas? Try using Paste Special > Values. Do you still get an error?

 

[dshafique]

they dont contain formulas, but when i do that, all the cells are just pasted white. they should be a mixture of red and green depending on their values

 

[MrKowz]

Ahh apologies, I misread the question. What is your conditional format formula?

 

[dshafique]

for red if cell is less than value in E column then turn red

for green if cell is greater than or equal to value in column E, turn green

for some reason, in vba, i made the formula to capture data from 2 cells to the left, but it always shows cell E2 when i open the manage rules section.

it also gives me one wrong value for some reason. one cell is 99.93% it should be green because its greater than the value 2 to the left (99.00%) but it comes up as red.

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="=RC[-2]"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=RC[-2]"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With

 

[MrKowz]

Ok, so I did some quick research, and it seems that conditional formats are tricky to work with. Basically, the cell's background has two properties. It's default color, which is defined in VBA as .Interior.ColorIndex, and the conditional format color, which is not a value we can return in VBA. At best, we can use VBA to copy the data over and replicate the state of the conditional format at the time of copying. Since the conditional format is cell = (2 columns to left) cell, this shouldn't be too bad to handle.

Lemme toss some code together to see if I can get something modular worked out for you.

 

[MrKowz]

Ok, give this a shot. This will allow you to do the following:

* Highlight the range you want to copy
* Run the macro
* The macro will ask you where you want to paste the data. Select the top cell where you want the data to be pasted to (i.e. if you're copying A1:A10 and you want to paste it to Z1:Z10, just choose Z1)

NOTE: This code is replicating the specific example you gave above. This will not work for other conditional formats.

Public Sub CopyPaste()
Dim srng        As Range, _
    drng        As Range, _
    rrng        As Range, _
    rowx        As Long
    
Set srng = Selection
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(rowx, 0)
        .Value = rrng.Value
        If .Value < rrng.Offset(0, -2).Value Then
            .Interior.ColorIndex = 3
        Else
            .Interior.ColorIndex = 50
        End If
    End With
    rowx = rowx + 1
Next rrng

End Sub

 

[MrKowz]

Addendum: here's some extra material around Conditional Formats and VBA.

Conditional Formatting Colors

 

[Rick Rothstein]

...and the conditional format color, which is not a value we can return in VBA.

In XL2010 and later, a DisplayFormat property was added to all range objects (Range, Cells, etc.) which will return the displayed color whether placed there manually or by Conditional Formatting, however, the property does not distinguish which source it's from.

 

[MrKowz]

In XL2010 and later, a DisplayFormat property was added to all range objects (Range, Cells, etc.) which will return the displayed color whether placed there manually or by Conditional Formatting, however, the property does not distinguish which source it's from.

Well that makes things quite nicer. Thanks!

Public Sub CopyPaste()
Dim srng        As Range, _
    drng        As Range, _
    rrng        As Range, _
    rowx        As Long
    
Set srng = Selection
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(rowx, 0)
        .Value = rrng.Value
        .Interior.ColorIndex = rrng.DisplayFormat.Interior.ColorIndex
    End With
    rowx = rowx + 1
Next rrng

End Sub