Conditional Formatting Formula to Color Rows Based on Column Value

1. Conditional Formatting Formula to Color Rows Based on Column Value

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...

2. RE: Conditional Formatting Formula to Color Rows Based on Column Value

http://www.mvps.org/dmcritchie/excel/condfmt.htm

Found the answer --> you need a helper column, unfortunately...

"SteveC" wrote:

> 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...
>

3. RE: Conditional Formatting Formula to Color Rows Based on Column Value

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,
--
Gary Brown
gary_brown@ge_NOSPAM.com

"SteveC" wrote:

> 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...
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1