VBA - Copy All Cells Highlighted from Conditional Formatting to Another Sheet

[luckee]

Hi, I have a workbook with over 20 sheets. AA1, AA2, AA3, AA4, etc with all the same headings. I have conditionally formatted cells to highlight RGB (180,198,231) if certain words are found in column F.

I want to create a macro that will go through all the sheets and find all the conditionally formatted rows then copy only columns F and AR from those sheets to a new consolidated sheet.

I'm not sure how to do this, macro newbie.

Thanks in advance!

 

[DanteAmor]

Check if this macro works for you:

Sub Macro_consolidate_by_color()
  Dim sh As Worksheet, shC As Worksheet
  Dim i As Long, k As Long
 
  Set shC = Sheets("consolidate")   'fit to your consolidate sheet name
  k = 1 
  For Each sh In Sheets
    If sh.Name <> shC.Name Then
      For i = 1 To sh.Range("F" & Rows.Count).End(3).Row
        If sh.Range("F" & i).DisplayFormat.Interior.Color = 15189684 Then
          k = k + 1
          shC.Range("F" & k).Resize(1, 39).Value = sh.Range("F" & i).Resize(1, 39).Value
        End If
      Next
    End If
  Next
End Sub

Note: If the macro doesn't work, then you should put the formula you use for conditional formatting here. And a couple of examples.

 

[luckee]

Check if this macro works for you:

Sub Macro_consolidate_by_color()
  Dim sh As Worksheet, shC As Worksheet
  Dim i As Long, k As Long
 
  Set shC = Sheets("consolidate")   'fit to your consolidate sheet name
  k = 1
  For Each sh In Sheets
    If sh.Name <> shC.Name Then
      For i = 1 To sh.Range("F" & Rows.Count).End(3).Row
        If sh.Range("F" & i).DisplayFormat.Interior.Color = 15189684 Then
          k = k + 1
          shC.Range("F" & k).Resize(1, 39).Value = sh.Range("F" & i).Resize(1, 39).Value
        End If
      Next
    End If
  Next
End Sub

Note: If the macro doesn't work, then you should put the formula you use for conditional formatting here. And a couple of examples.

Hi DanteAmor, thanks for the code ! it works to copy and paste the highlighted rows. is there a way to only copy and paste specific columns column F and column I (not everything starting from column F) and starting from cell B2 of the "consolidate" tab instead of F2?

thanks again !

 

[DanteAmor]

copy and paste specific columns column F and column I (not everything starting from column F) and starting from cell B2 of the "consolidate" tab instead of F2?

Try:

Sub Macro_consolidate_by_color()
  Dim sh As Worksheet, shC As Worksheet
  Dim i As Long, k As Long
  
  Set shC = Sheets("consolidate")   'fit to your consolidate sheet name
  
  k = 1
  For Each sh In Sheets
    If sh.Name <> shC.Name Then
      For i = 1 To sh.Range("F" & Rows.Count).End(3).Row
        If sh.Range("F" & i).DisplayFormat.Interior.Color = 15189684 Then
          k = k + 1
          shC.Range("B" & k).Value = sh.Range("F" & i).Value
          shC.Range("C" & k).Value = sh.Range("I" & i).Value
        End If
      Next
    End If
  Next
End Sub

 

[luckee]

Try:

Sub Macro_consolidate_by_color()
  Dim sh As Worksheet, shC As Worksheet
  Dim i As Long, k As Long
 
  Set shC = Sheets("consolidate")   'fit to your consolidate sheet name
 
  k = 1
  For Each sh In Sheets
    If sh.Name <> shC.Name Then
      For i = 1 To sh.Range("F" & Rows.Count).End(3).Row
        If sh.Range("F" & i).DisplayFormat.Interior.Color = 15189684 Then
          k = k + 1
          shC.Range("B" & k).Value = sh.Range("F" & i).Value
          shC.Range("C" & k).Value = sh.Range("I" & i).Value
        End If
      Next
    End If
  Next
End Sub

that's perfect, thank you so much !