View Single Post
  #4   Report Post  
Old July 6th 19, 02:46 PM posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,788
Default Identify rows if in the row one of the cells within a range is colored

Hi Johan,

Am Thu, 4 Jul 2019 21:46:10 -0700 (PDT) schrieb JS SL:

It taste to a bit more (if you like).
The next step step is to hide the columns in the range F till BZ if in the rows 3 (not 2 but 3) till last of that column is no colored cell.
After this rule I have a quick view on only the colored rows with the unique rownrs, but also only the columns that have a marked cell.
Makes life easier.


Sub Test()
Dim myRow As Range, rngC As Range, myRng As Range
Dim i As Integer, myCnt As Integer, Counter As Integer
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For Each myRow In .Range("F1:BZ" & LRow).Rows
Counter = 0
For i = .Columns("F").Column To .Columns("BZ").Column
If .Cells(myRow.Row, i).Interior.ColorIndex < xlNone Then
.Cells(myRow.Row, 1) = 1
Exit For
Counter = Counter + 1
End If
If Counter = 73 Then .Cells(myRow.Row, 1) = 0

For i = 1 To LRow
myCnt = Application.CountIf(.Range("E1:E" & LRow), .Cells(i, "E"))
If myCnt 1 Then
Set myRng = .Cells(i, 1).Resize(myCnt)
If Application.CountIf(myRng, 1) 0 Then
myRng = 1
i = i + myCnt - 1
End If
End If
For Each rngC In .Range("B3:BZ3")
If rngC.Interior.ColorIndex = xlNone Then rngC.EntireColumn.Hidden = True
End With
End Sub

Claus B.
Office 2016