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?
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
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
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
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?
Thanks Anil for your useful tips!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.