Conditional Formatting Formula to Color Rows Based on Column Value

S

SteveC

I've tried, but I can't figure it out. How to use conditional formatting to
alternatively color rows based on values in a column?

For example, in B13:B3000, I have values like this:

Col B Row color I'd lilke
Apples Green
Apples Green
Pears White
Pears White
Pears White
Pears White
Bananas Green
Monkeys White
Monkeys White
Giraffes Green etc...

Thanks for your help...
 
G

Gary L Brown

2 ways of doing this.
1) Use a 'cheat' column and conditional formatting.
Advantage: If the data changes, the formatting changes.
Disadvantage: Having an extra column with a formula
2) Use a macro.
Advantage: No extra columns or formulas to maintain.
Disadvantage: If the data changes, the formatting doesn't change unless
the macro is re-run.

Using option 1...
- Insert a blank column in Column A
- In Cell A1, put the number 1
- in Cell A2, put the formula...
=IF(C2=C1,A1,A1*-1)
It is comparing Apples to Pears to Bananas to Monkeys to find out when the
data changes. The value in column A will change from 1 to -1 everytime the
data switches from apples to pears, etc.
- Copy the formula in A2 down to the bottom of the data.
- Create the conditional formatting...
- highlight the entire worksheet
- Format>Conditional Formatting...
- Condition 1: Formula is
=$A1:$A1=1
- Format>Patterns>Green
- All rows that have a '1' in Column A will now turn green.

HTH,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top