粘贴条件格式 [英] Pasting Conditional Formatting

问题描述

我正在尝试从工作表 wsHR 中的列复制值和条件格式，并将其粘贴到 wsHH 中.

I'm trying to copy the values and conditional formatting from a column in the sheet wsHR and paste them into wsHH.

使用下面的代码粘贴值，但不粘贴格式.

With the code below the values are pasted, but the formatting is not.

我在 wsHR 中添加了无条件的格式，并且可以很好地将其复制过来.

I added formatting into wsHR that isn't conditional, and it works fine copying that over.

是否可以粘贴条件格式?

Is there a way to paste conditional formatting?

Private Sub CommandButton1_Click()

'Set variables
Dim LastRow As Long
Dim wsHR As Worksheet
Dim wsHH As Worksheet
Dim y As Integer

'Set row value
y = 4

'Set heavy chain raw data worksheet
Set wsHR = ThisWorkbook.Worksheets(4)
'Set heavy chain hits worksheet
Set wsHH = ThisWorkbook.Worksheets(6)

'Optimizes Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Finds last row
With wsHR
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Iterates through rows in column A, and copies the row into proper sheet depending on "X" in PBS/KREBS
For i = 4 To LastRow
    'Checks for "X" in PBS
    If VarType(wsHR.Range("AD" & i)) = 8 Then
        If wsHR.Range("AD" & i).Value = "X" Or wsHR.Range("AE" & i).Value = "X" Then
            With wsHH
                wsHR.Range("A" & i).Copy
                .Range("A" & y).PasteSpecial Paste:=xlPasteFormats
                .Range("A" & y).PasteSpecial Paste:=xlPasteValues
                'Range before PBS/KREBS
                .Range("B" & y & ":AC" & y).Value = wsHR.Range("B" & i & ":AC" & i).Value
                'Adds space to keep formulas for PBS/KREBS
                'Range after PBS/KREBS
                .Range("AG" & y & ":AW" & y).Value = wsHR.Range("AG" & i & ":AW" & i).Value
            End With
            y = y + 1
        End If
    End If
Next i

'Message Box when tasks are completed
MsgBox "Complete"

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

我不能在第二张表 wsHH 中使用相同的条件格式设置规则，因为并非 wsHR 中的所有值都被粘贴了.条件格式基于重复项.

I cannot use the same conditional formatting rules in the second sheet, wsHH, because not all of the values from wsHR are pasted. The conditional formatting is based on duplicates.

推荐答案

找到了一种变通方法来获取格式.以前，如果不进行大量额外的工作，就无法从VBA中的条件格式访问内部颜色(

Found a work-around to get the formatting. Previously, you were not able to access the interior color from conditional formatting in VBA without going through a lot of extra work (see here). However, I discovered as of Excel 2010, this was changed (see here). Since I'm using Excel 2013, I am able to use .DisplayFormat to find the interior color regardless of formatting (see here).

使用此命令，我进行了更改:

Using this, I changed:

With wsHH
  wsHR.Range("A" & i).Copy
  .Range("A" & y).PasteSpecial Paste:=xlPasteFormats
  .Range("A" & y).PasteSpecial Paste:=xlPasteValues
  'Range before PBS/KREBS
  .Range("B" & y & ":AC" & y).Value = wsHR.Range("B" & i & ":AC" & i).Value
  'Adds space to keep formulas for PBS/KREBS
  'Range after PBS/KREBS
  .Range("AG" & y & ":AW" & y).Value = wsHR.Range("AG" & i & ":AW" & i).Value
End With

对此:

With wsHH
  'Range before PBS/KREBS
  .Range("A" & y & ":AC" & y).Value = wsHR.Range("A" & i & ":AC" & i).Value
  'Adds space to keep formulas for PBS/KREBS
  'Applying background CF color to new sheet
  If wsHR.Range("A" & i).DisplayFormat.Interior.ColorIndex > 0 Then
    .Range("A" & y).Interior.ColorIndex = 3
  End If
  'Range after PBS/KREBS
  .Range("AG" & y & ":AW" & y).Value = wsHR.Range("AG" & i & ":AW" & i).Value
End With

我不再复制和粘贴值.相反，我使用 .Value 来设置值，就像处理该行中的其他单元格一样，然后使用 If wsHR.Range("A"& i)的结果.DisplayFormat.Interior.ColorIndex>0然后确定是否应该格式化第二张工作表的单元格.

I am no longer copying and pasting values. Instead, I set the values using .Value like I had been for the other cells in the row, and then use the outcome of If wsHR.Range("A" & i).DisplayFormat.Interior.ColorIndex > 0 Then to determine if the second sheet's cell should be formatted.

这篇关于粘贴条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！