Read all sheets to find cells with colour

Hello there,

I would like to detect whether there’s any red cell in any sheet of an excel file (including the hidden sheet). If red cell exists in any sheet, I would like to get the sheet name and cell address to populate them in a summary report later.

Any advice on what is the best way to do this?
If it is macro, how should the script looks like?

Thank you!


Please try this macro…it will read all sheets and will write the sheetname and cell address in a separate sheet in the same file

Sub FindRedFillCells()
    Dim ws As Worksheet
    Dim destSheet As Worksheet
    Dim cell As Range
    Dim lastRow As Long
    Set destSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    destSheet.Name = "RedFillCells"
    destSheet.Range("A1").Value = "Sheet Name"
    destSheet.Range("B1").Value = "Cell Address"
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If cell.Interior.Color = RGB(255, 0, 0) Then
                lastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row + 1
                destSheet.Cells(lastRow, 1).Value = ws.Name
                destSheet.Cells(lastRow, 2).Value = cell.Address
            End If
        Next cell
    Next ws
End Sub




1 Like

Hi @Anil_G ,

That’s amazing! Your codes worked well! :grinning:

One small point, do you mind to advice how can I tailor the code to the below?

Instead of writing the summary of cell address in a new sheet of the same excel file, I would like to write the result in a specific excel file that I have created, the file is named as “Summary Table.xlxs”, under column D with below format

The result which I got by running your codes was having a summary sheet named “RedFillCells” in the same excel which the red cells were identified.


After running the macro read the sheet and store in dt (datatable variable) and then use the following to convert to string as you need and write to excel you need

RequiredString = String.Join(",",dt.AsEnumerable.Select(function(x) x("Sheet Name").ToString + "(" + x("Cell Address").ToString + ")"))

if you dont need the sheet you can delete as well after reading

Hope this helps



Do in include this in an assign activity? What variable type shall put?

RequiredString = String.Join(“,”,dt.AsEnumerable.Select(function(x) x(“Sheet Name”).ToString + “(” + x(“Cell Address”).ToString + “)”))


Yes in assign you can have it…variable type would be string…requiredstring is the variable I gave…you can change…

that string will be in the required format you need …which you can write to your target excel



I created a datatable variable

and used the assign activity

String.Join(“,”,ColouredCells.AsEnumerable.Select(function(x) x(“Sheet Name”).ToString + “(” + x(“Cell Address”).ToString + “)”))

However, there’s an error saying Option Strict On disallows late binding.
May I know how could I correct this error?


Can you remove and re add…I dont see any exception

is coloredcells a datatable and readcells a string type?



The required string is okay now, but the colouredcell seems to have an error as a datatable?


  1. You need to run the invoke vba …there is no output for that activity that I am sending
  2. After that use read range activity and read the data into a datatble colorcells in your case…not in invoke vba


Thanks Anil for your useful tips!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.