Anyone help on below query,
I have set of 25 sheets in a workbook. Based on the column name in a sheet, if the value is greater than 0 in that column, that sheet tab needs to get colored.
I have the code as below,
But am not able to highlight and color the tab, which has the column value greater than 0.
I have set of 25 sheets in a workbook. Based on the column name in a sheet, if the value is greater than 0 in that column, that sheet tab needs to get colored.
I have the code as below,
Code:
Sub Click()
Dim sh As Worksheet, ws As Worksheet, x As Long, i As Long, rngCIK As Range
With ThisWorkbook
.Sheets.Add(Before:=.Sheets(1)).Name = "Report"
End With
Set ws = ActiveSheet
i = 2
ws.Range("A1:B1").Value = Array("CIK", "Count")
For Each sh In Sheets
If Not sh Is ws Then
Set rngCIK = sh.Rows(1).Find("CIK", , xlValues, xlWhole, 1, 1, 0)
If Not rngCIK Is Nothing Then
x = Application.CountA(rngCIK.EntireColumn) - 1
ws.Rows(i).Range("A1:B1").Value = Array(sh.Name, x)
i = i + 1
End If
End If
Next sh
End Sub
But am not able to highlight and color the tab, which has the column value greater than 0.
Last edited by a moderator: