Forum Discussion
Cell Colour Changed Based On Two Values
Hello, I have a query…
A1 could contain either the text ‘Team One’ or ‘Team Two’.
B1 will contain a number.
I want the colour of B1 to change, dependant firstly on what Text is in A1 and then dependant on what range the number belongs to in B1.
For Example six different colours would represent these 6 ‘rules’…
A1 = Team One , B1 = 0 to 12
A1 = Team One , B1 = 13 to 18
A1 = Team One , B1 = 19 to 99
A1 = Team Two , B1 = 0 to 25
A1 = Team Two , B1 = 26 to 35
A1 = Team Two , B1 = 36 to 99
…can anyone assist me? I currently have two tables, one for each team, and then conditional formatting dependant on the numbers. But it would be better for me if I could just have one table but the text in A1 would also affect the colour of B1 not just the number?
Thanks in advance!
- Using 6 custom conditional formatting rules you can do this. Select column B then go to conditional formatting, add a new rule, select custom/user defined rule and enter:
=($A1="Team One")*($B1<13)*($B1>=0)
then select the color for that first category.
Use 'Duplicate Rule' (if you have it) and then edit the formula and color format for each of the next 5.NOTE that by applying the $ before the A and B as both Lorenzo and OliverScheurich and I did in the formula, you can make the 'Applied To' range extend over many/all columns to have more or all the rows get highlighted in that color.
Just make sure the the row # you use (as in A1 points to row 1) is that same row as the top row in the Applied To range. So if you only highlight A20:D40 then use $A20 and $B20 instead.
Hi TheLifeOfJon
The 1st rule is:
=AND($A1="Team One", $B1 >=0, $B1 <=12)once you've understood it, the others should be easy to set up - sample attached
