Read all sheets to find cells with colour

[WS_Chai]

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!

[Anil_G (Anil Gorthi)]

@WS_Chai

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

Cells:

output:

cheers

[WS_Chai]

Hi @Anil_G ,

That’s amazing! Your codes worked well!

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

[WS_Chai]

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.

[Anil_G (Anil Gorthi)]

@WS_Chai

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

cheers

[WS_Chai]

@Anil_G.

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 + “)”))

[Anil_G (Anil Gorthi)]

@WS_Chai

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

cheers

[WS_Chai]

@Anil_G

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?

[Anil_G (Anil Gorthi)]

@WS_Chai

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

is coloredcells a datatable and readcells a string type?

cheers

[WS_Chai]

@Anil_G,

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

[Anil_G (Anil Gorthi)]

@WS_Chai

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

Cheers

[WS_Chai]

Thanks Anil for your useful tips!