Conditional format-change format when rowheading changes

[FredMcStaire]

A data set (a1:d100) has auto manufacturers alphabetised in column A. How do I apply a condition so that the entire data set for each manufacturer has its own colour? (real data set is much more complicated and has about 300 changing manufacturers) Using Excel 2007. Thanks!

Manufacturer	Colour	Size	Price
Holden	Red	Mid	$500
Holden	Blue	Mid	$500
Ford	Green	Large	$550
Ford	Purple	Small	$400
Toyota	Black	Mid	$450

<TBODY>
</TBODY>

 

[potter_ricky]

Reasonably straight forward using conditional formatting rules.

Assuming the data you mention in your post is in the range A1:D6;

1.) Select the range A2:D2

2.) From the ribbon click Home > Conditional Formatting > Manage Rules

3.) Click New Rule and select Rule Type 'Use a formula to determine which cells to format'

4.) In the 'Format values where this formula is true:' enter: =$B2="Red"

5.) Click the 'Format...' button select the 'Fill' tab, click the appropriate color and click ok

6.) Click ok

7.) Repeat steps 3 - 6 for amending the formula in 4 for each color that will be used

8.) When all colors have been added click 'Apply' and then 'Ok'

9.) From the ribbon click Home > Format Painter

10.) Select range A3:D6

Hope that helps

 

[FredMcStaire]

Thanks for the quick response potter_ricky - works a treat.
Can I throw a bit of scope creep at you? How would I shade the rows for every other manufacturer? Similar to applying shading for every other row. For example using the same data set, shading the Holden rows, not shading Ford, shading Toyota, not shading the next and so on? Is there a way to do this with a generic formula without making a condition for each manufacturer?
Thansk again for your help.

 

[potter_ricky]

Are you able to add a column to your data? If so:

In cell E1 enter Test

In Cell E2 add the formula: =IFERROR(IF(A2=A1,E1,NOT(E1)),TRUE)

Copy E2 down the range E2:E6

Amend the conditional formatting rules in point 4 of my above post to: =AND($B2="Red",$E2=TRUE)


If you can't add an extra column I think you may have to go down the VBA route...

 

[Andrew Poulsom]

To shade alternate manufacturers you can use the conditional formatting formula (for row 2 copied down):

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

 

[FredMcStaire]

Thanks both to Andrew and potter_Ricky.
Andrew, the formula you gave seems to work fine for the first 4 manufacturers. However, if I extend the formatting to the full list ( ~1000 rows with between 100 and 300 manufacturers), it starts skipping around a bit. Each manufacturer can have between 1 and 10 rows. which varies week by week. Is this an array formula? It looks like its saying count what number manufacturer this is, and if its odd, colour it, if its even, don't. Thanks

 

[Andrew Poulsom]

Is each manufacturer in a contiguous range like in your sample data? The formula counts the number of unique manufacturers and highlights if the count is an odd number.