List Excel cells with red text

We are not fans of VBA but sometimes there is just no other (quick) way. A great example is where you have something like below where you need to list Excel cells with red text e.g all the cells that have a red 1 in them. The 1 is easy to do in formula Excel, but it is much harder for a normal Excel formula to ‘see’ a color, in this case the red font.

Now, because of tools like ChatGPT, it is easy to do quick VBA tasks in Excel for average Excel users.

Questions asked of Chat GPT (NB NB)

First the requirement. We received a spreadsheet with multiple sheets where text in red meant that an event occurred and we needed to extract the sheet name and some other details of these red text cells. Options were to manually work through every sheet and visually find the solution OR ask Chat GPT to help (we could have just built the VBA but we didn’t).

The key with AI is how you ask the questions and the refinements you make as you go along.

So our first question was:

Create VBA code to find all the cells with red text in a worksheet and create a report of the cell reference, the data in column c of the same row the cells were found and the data in row one of the same column the cells were found. 

The result worked ok but we needed to refine it so we said

Edit the above to name the sheet that the report is created in, the same name as the sheet being searched with the word report at the end.

Again, an improvement but we needed some additional information

To the above add to the report the data in column A of the same row as the cells with the red text

Another update

Edit the above to run the code on all sheets in the workbook and create reports for each sheet.

And one more to get it just right.

Edit the above to put the report immediately after the sheet it ran the code on

VBA code to document all cells with red text

The resultant code from all these questions was:

Sub FindRedTextAllSheets()
Dim ws As Worksheet
Dim cell As Range
Dim redCells As Collection
Dim redCell As Range
Dim report As Worksheet
Dim lastRow As Long
Dim rowIndex As Long
Dim colIndex As Long

For Each ws In ThisWorkbook.Worksheets
Set ws = ThisWorkbook.Worksheets(ws.Name)

    Set report = ThisWorkbook.Worksheets.Add(After:=ws)

    report.Name = ws.Name & " Report"

    Set redCells = New Collection

    For Each cell In ws.UsedRange
        If cell.Font.Color = RGB(255, 0, 0) Then ' Adjust the RGB values if needed
            redCells.Add cell
        End If
    Next cell

    report.Range("A1").Value = "Cell Reference"
    report.Range("B1").Value = "Data in Column A"
    report.Range("C1").Value = "Data in Column C"
    report.Range("D1").Value = "Data in Row 1"

For Each ws In ThisWorkbook.Worksheets
    Set ws = ThisWorkbook.Worksheets(ws.Name)

    Set report = ThisWorkbook.Worksheets.Add(After:=ws)

    report.Name = ws.Name & " Report"

    Set redCells = New Collection

    For Each cell In ws.UsedRange
        If cell.Font.Color = RGB(255, 0, 0) Then ' Adjust the RGB values if needed
            redCells.Add cell
        End If
    Next cell

    report.Range("A1").Value = "Cell Reference"
    report.Range("B1").Value = "Data in Column A"
    report.Range("C1").Value = "Data in Column C"
    report.Range("D1").Value = "Data in Row 1"

    rowIndex = 2 ' Start writing from row 2
    For Each redCell In redCells
        colIndex = redCell.Column

        report.Cells(rowIndex, 1).Value = redCell.Address(False, False)

        report.Cells(rowIndex, 2).Value = ws.Cells(redCell.Row, "A").Value

        report.Cells(rowIndex, 3).Value = ws.Cells(redCell.Row, "C").Value

        report.Cells(rowIndex, 4).Value = ws.Cells(1, colIndex).Value

        rowIndex = rowIndex + 1
    Next redCell

    report.UsedRange.Columns.AutoFit

    Set redCells = Nothing
    Set report = Nothing
    Set ws = Nothing
Next ws

End Sub

Rules when using AI to create code for Excel

Create a copy of the spreadsheet to work with ESPECIALLY if you macro is intended to make changes (which may go horribly wrong and there is no undo option after running a VBA macro).

Rather break your macros into small steps. AI tends to do little jobs well, but can fall apart with bigger tasks (mostly because of the humans poor description of what should be done.

How to run ChatGPT VBA on your spreadsheet

Remember to work off a copy- ALWAYS keep the original as you received it

The code that Chat GPT creates needs to be put somewhere. as Chat GPT says:

1. Open the VBA editor in Excel by pressing Alt + F11.
2. Insert a new module by clicking Insert and then selecting Module.
3. Copy and paste the code provided into the module as is.
4. Close the VBA editor.
5. Run the macro by pressing Alt + F8, selecting the correct macro, and clicking Run.

Related

Excel negative numbers in red (or another colour)

Counting or summing coloured cells in Excel- formula