UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Cell Coloring, Office 2007    
 
   
ccman69
post Feb 22 2012, 11:02 AM
Post #1

UtterAccess Veteran
Posts: 303
I have a spread sheet with a series of duplicates. Some of the duplicates can be as many as 5 rows or only 2 rows. I have the data sorted so the duplicates are together. What I would like to do either in a conditional formatting or via VBA is to alter the colors of the rows when the data changes. Example: If I have 5 rows that contain the exact same data, make those 5 rows a color and the next 3 rows are a different set of duplicates, they will remain not color filled. Then the next set of duplicates would again be colored the same as the first 5. I have 1800 rows that I need to alternate. You help is appreciated.
Go to the top of the page
 
+
MedicalServices
post Feb 22 2012, 12:29 PM
Post #2

UtterAccess Enthusiast
Posts: 55
This should do what you are trying to accomplish:

CODE
Public Sub AlternateColorsByRows()
Dim AlternatingColors As Collection, ColorChoice As Collection
Dim x As Range
Dim y
Dim C As Integer
Set AlternatingColors = New Collection
Set ColorChoice = New Collection
''Assuming the list resides in column "A"
ThisWorkbook.ActiveSheet.Range("A1", Range("A1").End(xlDown)).Select
On Error GoTo ERR
i = 1
For Each x In Selection.Cells
    AlternatingColors.Add x, CStr(x)
    If i Mod 2 > 0 Then
        C = 27 ' change to suit color value desired
        ColorChoice.Add C, CStr(x)
    Else
        C = 32 ' change to suit color value desired
        ColorChoice.Add C, CStr(x)
    End If
    i = i + 1
ReEnter:
Next

For Each x In Selection.Cells
    For Each y In AlternatingColors
        If x = y Then
            x.EntireRow.Interior.ColorIndex = ColorChoice(CStr(x))
        End If
    Next
Next
            

Exit Sub
ERR:
Resume ReEnter
End Sub


Let me know if it doesn't work out for you...
HTH
Go to the top of the page
 
+
ccman69
post Feb 22 2012, 03:06 PM
Post #3

UtterAccess Veteran
Posts: 303
Thanks, that worked great, except that I had to take out the "ThisWorkbook" part and is the a way to color only a specific range of columns. For instance only Columns (A thru O). Thanks Again.
Go to the top of the page
 
+
MedicalServices
post Feb 22 2012, 03:34 PM
Post #4

UtterAccess Enthusiast
Posts: 55
Yes you can change
CODE
x.entirerow.interior.colorindex

to
CODE
ActiveSheet.Range(x, x.Offset(0, 14)).Interior.ColorIndex = ColorChoice(CStr(x))

to color only columns A thru O
Hope this helps out...
Go to the top of the page
 
+
dflak
post Feb 23 2012, 02:04 PM
Post #5

Utter Access VIP
Posts: 3,551
From: North Carolina
You can do this with conditional formatting if you are willing to use a helper column. If your list of values is in column A then =match(A1,A:A,0) = row() will yield TRUE if the value is the first occurance and FALSE if it has appreared in a previous row.

Well, I guess you could write the formula in the conditional formatting definition and forget about the helper column.
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 12:09 AM