• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formating

A

abid.aries

New Member
Dear Seniors,


I am new to this forum and I have more than 5000 records in an excel sheet, and each record is in 10 rows and 3 column. Now what my boss is asking to me is that on when company name changes there should be a change color whole company data. I tried conditional formatting but do not know how to do this. Please help me out. If anybody require sample data, I will provide
 
Hi Abid,


Welcome to the fourms! please upload a sample file so that we may provide and appropriate solution.


Regards,
 
Hi Faseeh


This is the data for your review. sorry I am new so do not know how to attach file with post.

[pre]
Code: 
Company Name	Address	City
Trees N Trends	1860 Us Highway 431	Boaz
State	Zip-Code	County
AL	35957-5900	Marshall
Website	Phone	Fax
www.treesntrends.com	2565938443	2565936590
Contact	Title	Gender
Donna Milner	Manager	Female
Employees	Sales Volume	SIC Code
12	1,608,000	599967
Company Name	Address	City
Arboretum	213 S Richard Arrington Jr Blv	Birmingham
State	Zip-Code	County
AL	35233-2123	Jefferson
Website	Phone	Fax
2053242405
Contact	Title	Gender
Raymond Osbunn	Owner	Male
Employees	Sales Volume	SIC Code
6	804,000	599967
Company Name	Address	City
Inside Accents	3507 Montgomery Hwy	Dothan
State	Zip-Code	County
AL	36303-2110	Houston
Website	Phone	Fax
3346789744	3346738814
Contact	Title	Gender
Bill Register	Manager	Male
Employees	Sales Volume	SIC Code
20	2,680,000	599967
Company Name	Address	City
Trees N Trends Inc	1008 Jordan Ln NW	Huntsville
State	Zip-Code	County
AL	35816-3024	Madison
Website	Phone	Fax
www.treesandtrends.com	2565339111	2565348343
Contact	Title	Gender
George Raugh	Manager	Male
Employees	Sales Volume	SIC Code
16	2,144,000	599967
Company Name	Address	City
B's Shop	3370 Atlanta Hwy	Montgomery
State	Zip-Code	County
AL	36109-2702	Montgomery
Website	Phone	Fax
3342608202
Contact	Title	Gender
Be Pham	Owner	Female
Employees	Sales Volume	SIC Code
2	268,000	599967
Company Name	Address	City
Design World	8059 Highway 72 W	Madison
State	Zip-Code	County
AL	35758-9564	Madison
Website	Phone	Fax
2567220086
Contact	Title	Gender
Dow Canup	Manager	Male
Employees	Sales Volume	SIC Code
8	1,072,000	599967
Company Name	Address	City
Silk Design	302 Carroll Ln	Anchorage
State	Zip-Code	County
AK	99517-0000	Anchorage
Website	Phone	Fax
9072430576
Contact	Title	Gender
Jan Eastberg	Owner	Female
Employees	Sales Volume	SIC Code
1	134,000	599967
Company Name	Address	City
Kiss The Bride	37704 Grayling Ct	Soldotna
State	Zip-Code	County
AK	99669-0000	Kenai Pen
Website	Phone	Fax
9072624089
Contact	Title	Gender
Mary Wilson	President	Female
Employees	Sales Volume	SIC Code
1	134,000	599967
[/pre]
Regards

Abid
 
Hi Abid,


If your data starts in row 1 with Company Name and then changes every 10 rows, then you could shade at row 10 each time with this


=MOD(ROW(),10)=1


Select your data, then go to CF > New Rule, paste the formula in and format the fill to your selected colour
 
Hi Abid ,


To add to what oldchippy has said , if you want that all 10 rows should be shaded alternately , then use the following formula :


=MOD((ROW()-ROW($A$2)),20)<10


assuming that your data starts on row 2.


Select your data range , go to Conditional Formatting , and enter the above formula.


Narayan
 
You must log in or register to reply here.
Back
Top