Conditional formatting for groups of different size

[methody]

Hi
I have a large grid with rows of 1's or 0's. Some of the 1's are single (ie surrounded by 0's -010) some are in groups of 2 (ie 2 1's beside each other - 0110) some are in groups of 3 etc 01110. up to groups of 8.

I wan to use conditional formatting so that the singles are blue, the doubles are red, the triples are green etc.

I thought it would be straightforward but it doesn't seem to be. I can manage the first 1 in each group but when you fill across problems occur. I tried use ifs, ands and or but to no avail.

any ideas
thanks

 

[offthelip]

I think the only way you can do that is by using vba, this code should do what you have asked for, I have only coded the case 1 to 3 but you can easily add more case and color subs

Sub test()
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(LastRow, LastCol))
For i = 2 To LastRow
' initialise column counter
ct = 0 ' note This relies on there only being 1 or 0 in column A
For j = 1 To LastCol
   If inarr(i, j) = 0 Then
          If ct > 0 Then
      nt = j - ct
      Select Case ct
      Case 1
       Call blue(Range(Cells(i, nt), Cells(i, j - 1)))
      Case 2
      Call red(Range(Cells(i, nt), Cells(i, j - 1)))
      Case 3
      Call green(Range(Cells(i, nt), Cells(i, j - 1)))
      End Select
     End If
     ct = 0
   Else
    ct = ct + 1  ' increment to 1 counter
   End If
Next j
' cater for the last coloumn
     If ct > 0 Then
      nt = j - ct
      Select Case ct
      Case 1
       Call blue(Range(Cells(i, nt), Cells(i, j - 1)))
      Case 2
      Call red(Range(Cells(i, nt), Cells(i, j - 1)))
      Case 3
      Call green(Range(Cells(i, nt), Cells(i, j - 1)))
      End Select
     End If

Next i

     
End Sub
Sub blue(rr As Range)
'
' blue Macro
'
    With rr.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
Sub red(rr As Range)
'
' red Macro
'

'
    With rr.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("J9:L9").Select
End Sub
Sub green(rr As Range)
'
' green Macro
'

'
    With rr.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

 

[StephenCrump]

Perhaps like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2		0	0	1	1	1	1	0	0	1	1	1	0	1	1	1	1	0	1	0	1	1	1	0	0	1	0
3		0	1	0	0	1	1	1	0	1	1	1	0	1	0	0	0	1	0	0	0	0	0	0	0	1	0
4		0	1	1	0	0	0	0	1	0	0	0	0	0	0	0	1	1	1	1	1	0	0	0	0	0	1
5		1	1	0	0	1	0	1	0	0	0	1	0	1	1	1	1	0	1	1	1	0	1	0	1	1	0
6		0	0	0	1	0	1	0	0	0	1	0	1	0	0	0	1	1	0	0	1	1	1	1	0	1	1
7		1	0	1	0	0	1	1	1	1	0	0	0	0	1	1	1	1	0	0	1	0	0	1	1	0	1
8		0	1	0	0	1	1	1	0	1	1	0	1	0	1	0	0	0	1	0	0	0	1	1	1	0	1
9		0	0	0	1	1	1	0	0	1	1	1	1	0	1	0	1	0	1	0	0	1	1	1	0	0	1
10		1	0	1	0	0	0	0	1	0	1	1	0	0	1	0	0	1	0	0	1	0	1	1	0	0	0
11		1	0	0	0	1	0	1	0	0	1	1	0	1	1	0	1	0	0	0	0	1	1	1	0	0	0
12		0	1	0	1	1	0	1	1	1	1	0	0	0	0	0	1	1	1	1	0	1	1	1	0	1	0
13		0	1	1	1	1	1	1	0	1	1	1	0	0	0	1	1	1	0	1	1	0	1	0	1	0	1
14		0	0	1	0	1	1	0	0	0	1	0	1	0	0	1	1	0	1	1	1	0	1	0	0	0	0
15		1	1	1	1	1	0	0	0	0	1	1	1	1	1	0	0	1	0	1	0	0	0	1	0	0	1
16		1	0	0	1	1	1	1	1	0	1	1	1	0	0	0	0	0	1	1	1	1	1	1	0	0	0
17		1	1	1	1	1	1	0	0	1	1	1	0	0	0	0	1	1	0	0	0	1	1	0	0	0	1
18		1	0	1	0	0	0	0	1	0	0	0	0	0	0	0	1	1	1	1	0	1	1	1	0	0	1
19		0	1	0	0	0	1	0	0	0	0	0	1	1	1	0	1	1	1	1	1	1	0	1	0	0	0
20		1	0	1	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	1	1	0	0	0	0	0
21		1	0	1	1	0	1	0	0	1	0	1	1	0	1	1	1	1	0	1	1	1	0	1	1	0	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AA21	Expression	=IF(B2=1,MATCH(0,C2:$AB2,)+COLUMN()-1-MAX(($A2:A2=0)*COLUMN($A2:A2)),0)=1	text	NO
B2:AA21	Expression	=IF(B2=1,MATCH(0,C2:$AB2,)+COLUMN()-1-MAX(($A2:A2=0)*COLUMN($A2:A2)),0)=2	text	NO
B2:AA21	Expression	=IF(B2=1,MATCH(0,C2:$AB2,)+COLUMN()-1-MAX(($A2:A2=0)*COLUMN($A2:A2)),0)=3	text	NO
B2:AA21	Expression	=IF(B2=1,MATCH(0,C2:$AB2,)+COLUMN()-1-MAX(($A2:A2=0)*COLUMN($A2:A2)),0)>=4	text	NO

 

[StephenCrump]

Oops. missed the last column. Change the rules to:

=IF(B2=1,MATCH(0,SIGN(B2:$AB2),)+COLUMN()-2-MAX(($A2:A2=0)*COLUMN($A2:A2)),0)=1 etc

 

[methody]

Thank you. they are both very helpful