Depending on Account cell is gray or white question.

[LiveUser]

I have account names and they are all in alphabetical order ascending. I want
to gray a row of cells on every other account name change.

Example of what I want:
A Row Color
Account 1 Gray
Account 1 Gray
Account 1 Gray
Account 2 White
Account 2 White
Account 2 White
Account 3 Gray
Account 3 Gray
Account 3 Gray

I know I can control-click on every other one but we all know with large
spreadsheets that can take forever. Is there a formula or something else that
can do this automatically/quickly?

I should mention, and I don't know if this will make a difference, but each
account may have different data in the same row as the account name.

For example:
A B Row Color
Account 1 Information 1 Gray
Account 1 Information 2 Gray
Account 1 Information 3 Gray
Account 2 Information 4 White
Account 2 Information 5 White
Account 2 Information 6 White
Account 3 Information 7 Gray
Account 3 Information 8 Gray
Account 3 Information 9 Gray

Thank you.

 

[Bernard Liengme]

Do they really occur in blocks of three? If so you can use Format |
Conditional Formatting with Formula Is:
=OR(MOD(ROW(),6)=1,MOD(ROW(),6)=2,MOD(ROW(),6)=3) and format to a grey
pattern. Select all the row (drag down the row headers with the mouse)
before using the Format | Conditional Formatting.
If the blocks of 3 is not a valid observation, please let us know
best wishes

 

[LiveUser]

It is not blocks of three. Anywhere from 1 to 50 or more.

Thank you.

 

[Teethless mama]

Conditional Formatting

=MOD(RIGHT(A2,LEN(A2)-FIND(" ",A2))+0,2)=1

 

[LiveUser]

I am fairly fresh at this so I have to ask what the process is on making this
happen?

Thank you.

 

[Gord Dibben]

Select the rows then Format>Condtional Formatting:Formula is:

Change the formula to this =MOD(RIGHT($A2,LEN($A2)-FIND(" ",$A2))+0,2)=1

Note the change to make A absolute by adding the $ sign

then Format to Gray Pattern and OK your way out.


Gord Dibben MS Excel MVP

 

[LiveUser]

I am sorry but for some reason I cannot get it to work...

 

[Gord Dibben]

No need to be sorry<g>

Let's see if we can walk you though it.

Where are you entering the formula?

It must be entered in the Formula is: dialog box in Format>Conditional
Formatting>Condition1

What rows have you pre-selected before entering the formula in CF?

If the top cell is A1 then change all instances of $A2 to suit.


Gord

 

[LiveUser]

That is exactly what I did. It still does not work. Would it have anything to
do with my version of Excel being 2000?

 

[Gord Dibben]

CF was introduced with Excel 97 so your version is OK.

I copied your data directly from your post and pasted to a new sheet.

Entered the formula in CF and got the desired effect.

Perhaps your data is not exactly as advertised?

Do you have the <space> as in

Account 1
Account 2
Account 3

If not, post a sample of the actual data.


Gord