Conditional Formatting - color groups based on row value

[djangeroo]

Hi,
I am new to the forum and impressed by the great advise that's out there.

This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.

Thanks for your help.

 

[NBVC]

Welcome to the Forum!

First, select all rows from Row 2 down that will be in your database.

Then go to Format|Conditional Formatting and Choose Formula Is from the drop Down.

In the formula box type =$A2<>$A1

Then click Format and select the Pattern Tab, choose Yellow

Then click Ok twice.

 

[djangeroo]

Thanks but I have tried that, it does not work for me.

The way this formula is coloring rows for me is only the first row whenever the value in column A is changing. What I need is every time the value is the same in column A, the rows should have one color, if the value changes, the row should alternate color. Any suggestions are appreciated!

 

[erik.van.geit]

Hello, djangeroo !
Welcome to the Board !!!!

conditional formatting for this would be very nice
can't seem to get the formula if possible

here is code which can do what you want

Option Explicit
Option Base 1

Sub color_same_data()
'Erik Van Geit
'051124 1754
'color cells with same contents in single column

Dim rng As Range
Dim LR As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim Arr As Variant
Dim ArrItem As String
Dim ArrRowNumbers() As Variant
Dim HL As Integer 'highlight

Const col = 1
Const FR = 2

If Cells(Rows.Count, col) <> "" Then LR = Rows.Count Else LR = Cells(Rows.Count, col).End(xlUp).Row
Set rng = Range(Cells(FR, col), Cells(LR, col))

Arr = rng.Value
i = 1
j = 1
    Do
    ArrItem = Arr(i, 1)
    k = i
        On Error Resume Next 'avoids bug at the end of the loop "Arr(i, 1)" when i > UBound(arr)
        Do
        i = i + 1
        Loop While ArrItem = Arr(i, 1)
        On Error GoTo 0
            'If k <> i - 1 Then     'enable these line to skip singles
            ReDim Preserve ArrRowNumbers(j + 1)
            ArrRowNumbers(j) = k + FR - 1
            ArrRowNumbers(j + 1) = i - 1 + FR - 1
            j = j + 2
            'End If                 'enable these line to skip singles
    Loop While i < LR

  
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

For i = 1 To j - 1 Step 2
HL = IIf(HL = 35, 36, 35)
Range(Cells(ArrRowNumbers(i), col), Cells(ArrRowNumbers(i + 1), col)).Interior.ColorIndex = HL
Next i

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

kind regards,
Erik

 

[NBVC]

Yeah, this one is not as easy as it seems,

probably, Erik's solution is best for this situation.

There has got to be a way to do it with conditional formatting in Excel though...just gotta find it

 

[djangeroo]

Thanks, Erik.
It works!
Now I am now wondering if it is possible to highlight the entire row.
And if it's possible to connect the run macro function with activating a cell.

Thanks again.

 

[erik.van.geit]

Now I am now wondering if it is possible to highlight the entire row.

this line highlights the cells
Range(Cells(ArrRowNumbers(i), col), Cells(ArrRowNumbers(i + 1), col)).Interior.ColorIndex = HL
make it

Range(Cells(ArrRowNumbers(i), col), Cells(ArrRowNumbers(i + 1), col)).EntireRow.Interior.ColorIndex = HL

 

[djangeroo]

Thank you!

 

[NBVC]

...There has got to be a way to do it with conditional formatting in Excel though...just gotta find it

I knew there was a way...

Thanks to kcmuppet who recommended this site for conditional formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm , I was able to manipulate one of the formulas to get the bands you needed.

Note: You have to create a helper column and house a 0 in the top cell above the table as in B1. You can always hide this row after you've entered the formulas.

Then insert formula in B2: =MOD(OFFSET($B2,-1,0)+ IF($A2<>OFFSET($A2,-1,0),TRUE,FALSE),2) and copy it down.

Then as for your conditional format, select all rows and in the Formula Is box, enter =$B1=1 and select the colour.

Book3
	A	B	C	D
1		0
2	John	1
3	John	1
4	Jack	0
5	Jack	0
6	Marg	1
7	Bart	0
8	Maggie	1
9	Maggie	1
10	Del	0
11	Del	0
Sheet2

 

[erik.van.geit]

WOW, NBVC !!
nice to share this, thank you !!

I'm after this one since about 8 months, seems so easy when it's finished

the formula can be shortened to

=MOD(OFFSET($B2,-1,0) --($A2<>OFFSET($A2,-1,0)),2)

I wonder if we could find something without helper column

best regards,
Erik