Color more than one cell on the same row when a cell has a certain numeric value

[dvp]

A B C D
1 Name 2009 2010 Total
2 Sam 400 600 1000
3 John 200 400 600
4 Eric 100 300 400


I'm working on a simple list, see example above, where a cells background color changes (in a simple traffic light system) in both the Total (column D) and Name (column A) when the cell value in D has certain numeric values, ie Green if total >=1000, Amber is between 500-999, Red<500. I've used a conditional formatting sequence using Ms Excel 2010 to highlight cells in D. However, how do i create a sequence or code so that the corresponding cell in A of the same row is colored the same way? For example, D2 is Green, and i want A2 to be Green as well? How do i do this?

 

[VoG]

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

 

[fromspenny]

In conditional format dialog box enter formula below as 3 seperate conditions and format as disired

Excel Workbook
	A	B	C	D	E	F
1	Name	2009	2010	Total
2	Sam	400	600	1000		=$D2>=1000
3	John	200	400	600		=AND($D2>=500,$D2
4	Eric	100	300	400		=$D2
Sheet

 

[dvp]

Thanks for your replies VOG and fromspenny, i did find some information similar to yours which highlights the whole row. What i wanted to know is if its possible to highlight just columns A and D, and not include B and C?

 

[VoG]

You can select columns A and D then apply Conditional Formatting using formulas like


=$D2>=1000

 

[fromspenny]

Thanks for your replies VOG and fromspenny, i did find some information similar to yours which highlights the whole row. What i wanted to know is if its possible to highlight just columns A and D, and not include B and C?

Highlight a2:a4 and d2:d4 and apply formulas from above

Excel Workbook
	A	B	C	D
1	Name	2009	2010	Total
2	Sam	400	600	1000
3	John	200	400	600
4	Eric	100	300	400
Sheet1

 

[dvp]

Thats worked a treat, thank you very much. I've applied it to my actual excel file, and both sets of cells are highlighted. One small problem, some of the totals do not highlight in the right color.

For example, i've set up conditional formatting for:

Green: =$D2>=100000
Orange: =AND($D2>=50000,$D2<100000)
Red: =$D2<50000

A total of 99,447.68, which should be orange but appears as green.

Why would this happen?

 

[fromspenny]

Thats worked a treat, thank you very much. I've applied it to my actual excel file, and both sets of cells are highlighted. One small problem, some of the totals do not highlight in the right color.

For example, i've set up conditional formatting for:

Green: =$D2>=100000
Orange: =AND($D2>=50000,$D2<100000)
Red: =$D2<50000

A total of 99,447.68, which should be orange but appears as green.

Why would this happen?

Check your figures are correct as the formatting works perfect for me

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	2009	2010	Total
2	Sam	400.00	100,000.00	100,400.00		TRUE	FALSE	FALSE	=$D2>100000
3	John	50,000.00	49,447.68	99,447.68		FALSE	TRUE	FALSE	= AND($D2>=50000,$D2
4	Harry	100.00	300.00	400.00		FALSE	FALSE	TRUE	=$D2
5	James	16,533.33	47,000.00	63,533.33		FALSE	TRUE	FALSE
6	John	50,000.00	20,000.00	70,000.00		FALSE	TRUE	FALSE
7	Kenny	20,000.00	40,000.00	60,000.00		FALSE	TRUE	FALSE
8	Lyn	20,000.00	85,000.00	105,000.00		TRUE	FALSE	FALSE
9	Fred	80,000.00	1,000.00	81,000.00		FALSE	TRUE	FALSE
10	William	25,000.00	26,000.00	51,000.00		FALSE	TRUE	FALSE
11	Sue	49,447.68	50,000.00	99,447.68		FALSE	TRUE	FALSE
12	Dan	15,483.33	20,000.00	35,483.33		FALSE	FALSE	TRUE
13	Abbie	15,333.33	10,000.00	25,333.33		FALSE	FALSE	TRUE
Sheet1

 

[dvp]

Thanks very much once again, i found the problem which was one of the cell numbers in the array was incorrect, works a treat!

 

[fromspenny]

No probs happy to help