View Single Post
  #6   Report Post  
Old July 6th 19, 05:38 PM posted to microsoft.public.excel.programming
JS SL JS SL is offline
external usenet poster
First recorded activity by ExcelBanter: May 2019
Posts: 43
Default Identify rows if in the row one of the cells within a range is colored

Claus, Thanks (!).

After run the macro all the columns F till BZ are hidden
But... There are only a few cells in the range 'F3 till BZ last row' with a collored cell. The macro hides them all.

Only the columns without any colored cell in one of the rows of that specific column should be set on hide (hide if at least one of the cells in all the used rows has a fill color).

Can you please have a look on it.
The part with the numbers wordks perfect !

regards, Johan.


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("F3:BZ3")
If rngC.Interior.ColorIndex = xlNone Then rngC.EntireColumn.Hidden = True
End With
End Sub