Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 4th 19, 07:02 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Identify rows if in the row one of the cells within a range is colored

Morning,

I nice question and hopely some people to help me

I've got a sheet with columns A till BZ and thousands of records.
Some people had remarked (manually fill patterncolor) some of the cells with a color (all different colors).
I need to know which rows have a colormarked cell.

The sheet is also build up with old/new datarecords. Those records are always combined with eachother based on the UniqueCode in column E.

In column A I like to get the number 1 (for selection) for the row if in the row in range column F till BZ one of the cells have a color (other then the normal standard blank). If in the row in column F till BZ none of the cells is colored then it gives the number 0.

Next challange...
Now you get rows with in column A the number 1 (some cells colored) and 0 (none of the cells colored).
If in column E de UniqueCode is the same and one of the rows with the same UniquCode has a 1 as result of the macro, then the result in A for the rows with a 0 must be overruled with a 1.

Start Situation
A E F till BZ
... UniqueCode1 (no colored cells)
... UniqueCode2 (no colored cells)
... UniqueCode2 (no colored cells)
... UniqueCode2 (one or more colored cells)

First step (cells with a color results in 1)
A E F till BZ
0 UniqueCode1 (no colored cells)
0 UniqueCode2 (no colored cells)
0 UniqueCode2 (no colored cells)
1 UniqueCode2 (one or more colored cells)

Next step (if UniqueCode is the same and one of the rows has 1 then all are 1)
A E F till BZ
0 UniqueCode1 (no colored cells)
1 UniqueCode2 (no colored cells)
1 UniqueCode2 (no colored cells)
1 UniqueCode2 (one or more colored cells)

Should be (very) nice if somebody can help me out.
Nice to have a solution for the normal manual selection.

regards, Johan

  #2   Report Post  
Old July 4th 19, 04:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,693
Default Identify rows if in the row one of the cells within a range is colored

Hi Johan,

Am Wed, 3 Jul 2019 23:02:39 -0700 (PDT) schrieb JS SL:

Start Situation
A E F till BZ
.. UniqueCode1 (no colored cells)
.. UniqueCode2 (no colored cells)
.. UniqueCode2 (no colored cells)
.. UniqueCode2 (one or more colored cells)

First step (cells with a color results in 1)
A E F till BZ
0 UniqueCode1 (no colored cells)
0 UniqueCode2 (no colored cells)
0 UniqueCode2 (no colored cells)
1 UniqueCode2 (one or more colored cells)

Next step (if UniqueCode is the same and one of the rows has 1 then all are 1)
A E F till BZ
0 UniqueCode1 (no colored cells)
1 UniqueCode2 (no colored cells)
1 UniqueCode2 (no colored cells)
1 UniqueCode2 (one or more colored cells)


try:

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
Else
Counter = Counter + 1
End If
Next
If Counter = 73 Then .Cells(myRow.Row, 1) = 0
Next

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)
myRng.Select
If Application.CountIf(myRng, 1) 0 Then
myRng = 1
i = i + myCnt - 1
End If
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old July 5th 19, 05:46 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Identify rows if in the row one of the cells within a range is colored

Thanks (as usual),

Works as wished !!. Allways amazing how you can fix a problem

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.

regards, Johan

  #4   Report Post  
Old July 6th 19, 02:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,693
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.


try:

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
Else
Counter = Counter + 1
End If
Next
If Counter = 73 Then .Cells(myRow.Row, 1) = 0
Next

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)
myRng.Select
If Application.CountIf(myRng, 1) 0 Then
myRng = 1
i = i + myCnt - 1
End If
End If
Next
For Each rngC In .Range("B3:BZ3")
If rngC.Interior.ColorIndex = xlNone Then rngC.EntireColumn.Hidden = True
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old July 6th 19, 02:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,693
Default Identify rows if in the row one of the cells within a range is colored

Hi again,

Am Sat, 6 Jul 2019 15:46:09 +0200 schrieb Claus Busch:

For Each rngC In .Range("B3:BZ3")
If rngC.Interior.ColorIndex = xlNone Then rngC.EntireColumn.Hidden = True
Next


sorry typo.
Change the first line to

For Each rngC In .Range("F3:BZ3")


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Old July 6th 19, 05:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
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
Else
Counter = Counter + 1
End If
Next
If Counter = 73 Then .Cells(myRow.Row, 1) = 0
Next

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)
myRng.Select
If Application.CountIf(myRng, 1) 0 Then
myRng = 1
i = i + myCnt - 1
End If
End If
Next
For Each rngC In .Range("F3:BZ3")
If rngC.Interior.ColorIndex = xlNone Then rngC.EntireColumn.Hidden = True
Next
End With
End Sub
  #7   Report Post  
Old July 6th 19, 06:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,693
Default Identify rows if in the row one of the cells within a range is colored

Hi Johan,

Am Sat, 6 Jul 2019 09:38:52 -0700 (PDT) schrieb JS SL:

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).


sorry, I misunderstood your question.

Change the lower part of the code in this way:

For j = 78 To 6 Step -1
Counter = 0
For i = 3 To LRow
If .Cells(i, j).Interior.Pattern < xlNone Then
Exit For
Else
Counter = Counter + 1
End If
Next
If Counter = LRow - 2 Then Columns(j).Hidden = True
Next


Regards
Claus B.
--
Windows10
Office 2016
  #8   Report Post  
Old July 7th 19, 04:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default Identify rows if in the row one of the cells within a range is colored

Claus,

Sorry,sorry, that I didn't explain it on the correct to understand it way.
Thanks a lot. Works as wished and clear to understand for other use.
Thanks !!!

regards, Johan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
creating rows of colored cells from adjacent numbers Roger on Excel Excel Programming 8 August 25th 08 12:31 PM
Identify a range of cells containing dates RJSohn Excel Worksheet Functions 2 August 24th 06 02:43 AM
Can I create a macro to identify and delete blank rows in a range? carlsondj Excel Programming 6 June 10th 05 12:38 AM
Excel - formula to calculate colored fill cells within a range wi. MA Excel Worksheet Functions 1 January 7th 05 04:06 PM
Cell right next to colored cells is automatically colored on entering a value Johan De Schutter Excel Programming 6 September 12th 03 05:31 PM


All times are GMT +1. The time now is 09:27 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017