conditional formatting based on other cell (over multiple cells)

[Benjaminmin]

Hi,

I have a list of names with a different count next to each of them looking like this:


a: 10
b: 5
c: 52
d: 8
e: 14

Ive applied conditional formatting for the 2nd column (with the numbers) so:

0-10 = shaded green
11-50 = shaded yellow
51+ = shaded red

That was no problem. But now I would like the names in the 1st column to get the same colour as their corresponding number.

I've tried conditional formatting with a "formula is= B1>10" etc. But that doesnt work since that only accounts for one cell, how do I make it dynamic so each cell takes its corresponding right cells color?

Thank you very much in advance!

 

[VoG]

See if this helps http://www.contextures.com/xlCondFormat02.html

 

[Andrew Poulsom]

If you copy the conditional formatting down the reference to B1 will adjust relatively (provided that it doesn't contain any $ signs).

 

[Benjaminmin]

See if this helps http://www.contextures.com/xlCondFormat02.html

That helped alot! Thank you VoG!

@Andrew, thank you for helping!! (unfortunately they all contain $)

REally appreciate the quick answer!

 

[Benjaminmin]

A small problem (sorry for bothering you again), but my condition for the values between 10 and 51 wont color at all.

Shouldnt =51>$J2&AND&$J2>10 work for that?

 

[Andrew Poulsom]

More like:

=AND(51>$J2,J2>10)

 

[Benjaminmin]

More like:

=AND(51>$J2,J2>10)

Thanks a million

 

[k_one]

Thanks for the note about the $ Andrew, I was having the same problem and didn't realise that taking out the $'s could fix it.