How do I conditional format multiple cells?

[neildexter]

I have created a spreadsheet to track online purchases. What I would like it to do is change the cells background colour red, if an item has been received and green if the item has been received.

I have data running from column A through to J. It is in cell J you log whether you have received an item or not by entering a Y for yes and N for no. I have Data Validation setup on column J, so you can only enter Y or N.

What do I need to do to make row cells A to J change colour depending on whether you have received the item or not?

The version of Excel I am using is Microsoft Excel Mac 2011.

 

[Akashwani]

Hi and welcome to MrExcel.

Seeing as column J will only ever have Y or N, fill ALL your rows Red and then use this Conditional Formatting formula...

=$J2="Y"

Example.........

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10		CF Formula
2	Bill	Red	6	2	5	5	7	3		Y		TRUE
3	Ben	Blue	2	2	10	7	4	9		N		FALSE
4	Bob	Green	6	8	2	1	10	4		N		FALSE
5	Don	Yellow	3	9	2	8	8	3		Y		TRUE
6	Den	Black	5	6	8	1	9	4		N		FALSE
7	Sam	Pink	3	6	10	8	8	10		Y		TRUE
8
Sheet5

To apply the CF formula....

Highlight your range from the top left cell to the bottom right cell.

Press alt, H, L , N
Click Use a formula etc and paste the above formula in the white box.
Click format and select green, click OK, OK

I hope that helps.

Ak

 

[neildexter]

When I hold ALT, H, L, N down it does not do anything other than put those letters into the cell.

 

[Charlie45]

press ALT but don't hold, then press H don't hold, press L don't hold, press N don't hold, that should put you in the conditional format menu

 

[neildexter]

When I do what you say it again just starts to type the letters into a cell. I am using Excel Mac version, not Windows version.

 

[Akashwani]

Hi,

Click your Home tab, click Conditional Formatting, click New Rule....
Click Use a formula etc and paste the above formula in the white box.
Click format and select green, click OK, OK

Ak