Conditional Format Help Needed

[Tmayeux]

Hi Everyone,
I need help with a conditional formatting issue. I basically what excel to look at my column A and every time the data changes I want it to highlight the cells a different color. Pretty much similar to the Subtotals function but I need cell color differation not numeric calculations. I want the cell backround shaded differnt colors, not the font different though like shown below. Also I do not have the same number of each account number so I can't say change every 5th time.

See below:

Col A
600000
600000
600000
605000
607000
607000

Thanks!!

 

[krishhi]

Hey there,

Did you tried the Conditional Formating, there you can set your chosen format the Custom Format.

 

[Tmayeux]

Yes,
I tried the Conditional Format Option. I could not find one that fit this particular problem though....

 

[krishhi]

Can you post the sample file?

 

[Tmayeux]

I am not sure I know how to attach a file on this board.

Basically Column A has random six digit numbers in it that repeats vertically. Some numbers repeat several times. Other columns I have have names and dollar amounts in them. I want to see color differences each time Column A changes to a different account number. I want every cell in Column A to be shaded. It does not matter if it just two colors alternating or if I have a differnt color for each GL number. See Below:

ColumnA ColumnB ColumnC
600000 US Foods 100.00
600000 US Foods 150.00
600000 US Foods 250.00
605000 Walmart 50.00
605000 Ace Hardware 100.00
605000 Sam's 800.00
900000 K-mart 55.00

 

[danzon]

=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=1

Assuming A1 is the first of your data... select A1 , choose conditional formatting with formula and enter the above....choose color

Copy that cell, select all of the below cells that may have data... press F5, click Special, and choose formats, hit OK....

your groups should now alternate color

 

[Tmayeux]

Danzon,

That sort of worked. It highlighted my first group of GL#'s but did not do anything to any of the other sets of GL#s.

 

[danzon]

hmmm... it is working for me using your sample data.... unless I am misunderstanding your request

 

[Tmayeux]

I verified that is what was in my formula bar just like your screen shot. When I scolled down I did notice that random cells were highlighed the chosen color. It did not highlight chuncks of account just random cells.

I had some trouble following your paste directions. When I copied the cell and highlighted the rest of my cells and hit F5 it only gave me the option to copy conditional formats. If I copied the cell and then right clicked paste special and then I could choose formats only.

 

[danzon]

Sorry....I can't figure out why it is not working for you.... it does work for me...I expanded my column A to add more random values...all grouped by color automatically as expected...