Sheet1 of the workbook to contain name of the other sheets based on certain criteria

Question

I've an excel worksheet which comprises of many sheet in turn let say around 5...I want to write a macro which will identify the presence of color say red in excel sheets Sheet2, Sheet3, Sheet4 and Sheet5 if there is any red color in any of the cell, it will report the sheet name in Sheet 1 of the workbook that Sheet<> so and so contains the red color in its any one of the cell.

Till yet i was only in position to identify that whether there is red color present in the particular sheet...but don't know how to report the corresponding sheet name in the sheet 1.

Sub CheckColor()
Dim Row
For Row = 2 To tsheet.UsedRange.Rows.Count
For chkcol = 1 To Sheet1.UsedRange.Columns.Count
If tsheet.Cells(Row, chkcol).Interior.ColorIndex = 3 Then
    'How to report the corresponding sheet name in sheet 1???
End If
Next
Next Row

End Sub

ApplePie ApplePie · Accepted Answer · 2012-12-09T03:27:53

First, I think using Range.Find() along with FindFormat would be much more efficient:

Private Sub test()
    Application.FindFormat.Clear
    Application.FindFormat.Interior.ColorIndex = 3

    Range("A1:C3").Find(What:="", LookAt:=xlPart, SearchFormat:=True).Value = "test"
End Sub

For your principal question, I think you first need to establish whether you want to execute the code for all sheets or for a subset. If it's for all sheets, then you can simply wrap up the code above inside a for each loop on the Worksheets collection.

If you need to execute this code for a subset of the worksheets, then you can either use a rule (first 4 characters = "abcd") or hard code them in an array.

Then, it's just a matter of checking that the range returned by Find() is not nothing which means success. If the condition is met, then the current sheet is to be used by the main sheet as you want it.

Sheet1 of the workbook to contain name of the other sheets based on certain criteria

1 Answers