This video tutorial demonstrates how to copy all coloured/highlighted cells or records to another worksheet using a VBA macro.  In our scenario we have a transaction database and we want to copy all red records (either cell background or font colour) to another worksheet.  You can download the featured file here and copy the featured VBA code below. Please note the featured file doesn’t include the code as I cannot upload macro-enable Excel files to this website.

 

LIVE ONLINE TRAINING COURSES WITH A LIVE TUTOR - AVAILABLE WORLDWIDE

Private or public courses available:
ONLINE EXCEL TRAINING (LIVE TUTOR): See Our Training Dates - Excel Fundamentals, Intermediate, Advanced and VBA Courses. 

ONLINE MS ACCESS TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE MS PROJECT TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE POWERPOINT TRAINING (LIVE TUTOR): See Our Training Dates


VIDEO TRAINING - LEARN AT YOUR OWN PACE

DEEP DIVE INTO LOOKUP FUNCTIONS - XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECTClick here to enrol

DEEP DIVE INTO DATE FUNCTIONS - TODAY, DATEDIF, WORKDAY, NETWORKDAYS, EOMONTH, EDATE, DATE, DATEVALUE, DAY, MONTH, YEAR, WEEKNUM, ISOWEEKNUM, WEEKDAY & YEARFRAC.
Click here to enrol

THE MS ACCESS FOR BEGINNERS' COURSE - Click here to enrol


FREE STUFF
70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf 

 

 

VBA code to copy cells with a red background

Sub CopyHighlightedTransactions()

Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet

Set ATransWS = Worksheets("All Transactions")
Set TransIDField = ATransWS.Range("A2", ATransWS.Range("A2").End(xlDown))
Set HTransWS = Worksheets("Highlighted Transactions")


For Each TransIDCell In TransIDField

    If TransIDCell.Interior.Color = RGB(255, 0, 0) Then
        
        TransIDCell.Resize(1, 10).Copy Destination:= _
            HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
            
    End If

Next TransIDCell

HTransWS.Columns.AutoFit

End Sub

VBA code to copy cells with a red font

Sub CopyColouredFontTransactions()

Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet
Dim x As Long

Set ATransWS = Worksheets("All Transactions")
Set TransIDField = ATransWS.Range("A2", ATransWS.Range("A2").End(xlDown))
Set HTransWS = Worksheets("Highlighted Transactions")


For Each TransIDCell In TransIDField

    If TransIDCell.Font.Color = RGB(255, 0, 0) Then
        
        TransIDCell.Resize(1, 10).Copy Destination:= _
            HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
            
    End If

Next TransIDCell

HTransWS.Columns.AutoFit

End Sub

Posted by Blue Pecan Computer Training