Help: format records (color) at each change in continious field

[Guest]

I want to toggle the color of the background for all the rcords at each
change of value in a specified field

 

[Guest]

Not sure that I understand the question. When you say toggle, I assume you
mean that the first time the cell is changed the color changes to yellow and
the next time the cell is changed the color changes to green and on the next
change the color toggles back to yellow. Is this assumption correct?

Do you mean that when you change a specified field in a record then the
entire record or row belonging to that field changes color?

Or do you mean that all records change color when you change one specific
field on the worksheet?

Do you want an On Change Event macro to do what you require and if so do you
need instructions to load it. (It would run automatically)

Regards,

OssieMac

 

[Guest]

so u can use conditional formating in our data rang, can u get more
information i can hel u

 

[Guest]

Sorry, I can see how the question was confussing after I read it again.

Similar to the subtotal function.
At each change in NAME sum AMOUNT OWED
except format the records above so its easy to see the seperation.
name address amount owed
joe address 6 format grey
joe address 4 format grey
joe address 3 format grey
jill address 5 no format
jim address 2 format grey
jim address 5 format grey

 

[Guest]

Hi,

Here is a macro that should do what you require. If you need instructions
for installing the macro then get back to me.

There are 2 lines of code to initialize the colIdx
('colIdx = 15 'Grey and colIdx = xlColorIndexNone).
Currently the macro is set to have no color in the first row. If you want
the color to start in the first row then remove the single quote from the
first line and place it at the start of the second line.

If you don’t like the colors I have used then you can look up colorindex
values. Select Help while in the VBA editor. (this is a different help to
worksheet help so make sure you are in the VBA Editor when you select Help).

In xl2007 enter colorindex as one word in the search and then select
colorindex property.

In earlier versions enter patterncolorindex as one word in the Answer Wizard
search and then select colorindex property.



Sub Alternate_Row_Color()
Dim rngName As Range
Dim colIdx As Integer
Dim i As Long

'Following assumes column header in row 1
'Therefore range assigned to variable starts at row 2
'Edit the sheet name between double quotes to match your sheet name
Set rngName = Sheets("Sheet1").Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))


'First row color set by following lines
'colIdx = 15 'Grey
colIdx = xlColorIndexNone

With rngName
'Color the first data row grey
.Cells(1, 1).EntireRow.Interior.ColorIndex = colIdx

'Starting at 2nd data row
For i = 2 To .Rows.Count
If .Cells(i) <> .Cells(i - 1) Then
If colIdx = 15 Then
colIdx = xlColorIndexNone
Else
colIdx = 15
End If
End If
.Cells(i).EntireRow.Interior.ColorIndex = colIdx
Next i
End With

End Sub



Regards,

OssieMac

 

[T. Valko]

This will work as long as there are no empty cells within your range. This
might affect performance if you have 1000's of rows.

Assume your data is in the range A1:C100
Select the range A1:C100
Goto Format>Conditional Formatting
Formula Is:

=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)

Click the Format button
Select the Patterns tab
Select the desired color
OK out

 

[T. Valko]

I should note that this formula is based on each group being unique.

 

[Guest]

Thank you ver much

MR Green

I should note that this formula is based on each group being unique.

 

[Guest]

This is very helpful
Thank you

Mr Green

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you ver much

MR Green