View Single Post
  #1   Report Post  
Old July 4th 19, 07:02 AM 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

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