Conditional Formatting

[krispatterson]

Hi guys

I've got a spreadsheet full of data that I want to run a colour-based conditional format against.


So, did a bit of research and came up with the following:
https://ibb.co/iRv3eF

Promise that's not a dodgy link, I've been posting here for a while!!


What I want it to do specifically is:
> look at K - if 0, colour A-Q red
- if 1, colour A-Q blue
- if 4, colour A-Q green
- if blank, A-Q no colour


What it's actually doing is looking at each sell to see if it's blank, and no colouring on each cell. I want it to be more specific than that and JUST make that decision on K.


Does that make sense??? Seems like an easy fix but I can't get my head around it.

Thanks

 

[etaf]

Re: Conditional Formatting - easy help!!

your range is only row 3 is that what you want ?

for red
=AND($K3=0, $K3 <> "")

otherwise it looks OK, just change the range to include more cells

 

[etaf]

Re: Conditional Formatting - easy help!!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2											0
3											1
4											2
5											3
6											4
7											5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =$K2=4	Abc
A2	2. / Formula is =$K2=1	Abc
A2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B2	1. / Formula is =$K2=4	Abc
B2	2. / Formula is =$K2=1	Abc
B2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C2	1. / Formula is =$K2=4	Abc
C2	2. / Formula is =$K2=1	Abc
C2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D2	1. / Formula is =$K2=4	Abc
D2	2. / Formula is =$K2=1	Abc
D2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E2	1. / Formula is =$K2=4	Abc
E2	2. / Formula is =$K2=1	Abc
E2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F2	1. / Formula is =$K2=4	Abc
F2	2. / Formula is =$K2=1	Abc
F2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G2	1. / Formula is =$K2=4	Abc
G2	2. / Formula is =$K2=1	Abc
G2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H2	1. / Formula is =$K2=4	Abc
H2	2. / Formula is =$K2=1	Abc
H2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I2	1. / Formula is =$K2=4	Abc
I2	2. / Formula is =$K2=1	Abc
I2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J2	1. / Formula is =$K2=4	Abc
J2	2. / Formula is =$K2=1	Abc
J2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K2	1. / Formula is =$K2=4	Abc
K2	2. / Formula is =$K2=1	Abc
K2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L2	1. / Formula is =$K2=4	Abc
L2	2. / Formula is =$K2=1	Abc
L2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M2	1. / Formula is =$K2=4	Abc
M2	2. / Formula is =$K2=1	Abc
M2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N2	1. / Formula is =$K2=4	Abc
N2	2. / Formula is =$K2=1	Abc
N2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O2	1. / Formula is =$K2=4	Abc
O2	2. / Formula is =$K2=1	Abc
O2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P2	1. / Formula is =$K2=4	Abc
P2	2. / Formula is =$K2=1	Abc
P2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q2	1. / Formula is =$K2=4	Abc
Q2	2. / Formula is =$K2=1	Abc
Q2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A3	1. / Formula is =$K2=4	Abc
A3	2. / Formula is =$K2=1	Abc
A3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B3	1. / Formula is =$K2=4	Abc
B3	2. / Formula is =$K2=1	Abc
B3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C3	1. / Formula is =$K2=4	Abc
C3	2. / Formula is =$K2=1	Abc
C3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D3	1. / Formula is =$K2=4	Abc
D3	2. / Formula is =$K2=1	Abc
D3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E3	1. / Formula is =$K2=4	Abc
E3	2. / Formula is =$K2=1	Abc
E3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F3	1. / Formula is =$K2=4	Abc
F3	2. / Formula is =$K2=1	Abc
F3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G3	1. / Formula is =$K2=4	Abc
G3	2. / Formula is =$K2=1	Abc
G3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H3	1. / Formula is =$K2=4	Abc
H3	2. / Formula is =$K2=1	Abc
H3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I3	1. / Formula is =$K2=4	Abc
I3	2. / Formula is =$K2=1	Abc
I3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J3	1. / Formula is =$K2=4	Abc
J3	2. / Formula is =$K2=1	Abc
J3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K3	1. / Formula is =$K2=4	Abc
K3	2. / Formula is =$K2=1	Abc
K3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L3	1. / Formula is =$K2=4	Abc
L3	2. / Formula is =$K2=1	Abc
L3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M3	1. / Formula is =$K2=4	Abc
M3	2. / Formula is =$K2=1	Abc
M3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N3	1. / Formula is =$K2=4	Abc
N3	2. / Formula is =$K2=1	Abc
N3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O3	1. / Formula is =$K2=4	Abc
O3	2. / Formula is =$K2=1	Abc
O3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P3	1. / Formula is =$K2=4	Abc
P3	2. / Formula is =$K2=1	Abc
P3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q3	1. / Formula is =$K2=4	Abc
Q3	2. / Formula is =$K2=1	Abc
Q3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A4	1. / Formula is =$K2=4	Abc
A4	2. / Formula is =$K2=1	Abc
A4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B4	1. / Formula is =$K2=4	Abc
B4	2. / Formula is =$K2=1	Abc
B4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C4	1. / Formula is =$K2=4	Abc
C4	2. / Formula is =$K2=1	Abc
C4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D4	1. / Formula is =$K2=4	Abc
D4	2. / Formula is =$K2=1	Abc
D4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E4	1. / Formula is =$K2=4	Abc
E4	2. / Formula is =$K2=1	Abc
E4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F4	1. / Formula is =$K2=4	Abc
F4	2. / Formula is =$K2=1	Abc
F4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G4	1. / Formula is =$K2=4	Abc
G4	2. / Formula is =$K2=1	Abc
G4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H4	1. / Formula is =$K2=4	Abc
H4	2. / Formula is =$K2=1	Abc
H4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I4	1. / Formula is =$K2=4	Abc
I4	2. / Formula is =$K2=1	Abc
I4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J4	1. / Formula is =$K2=4	Abc
J4	2. / Formula is =$K2=1	Abc
J4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K4	1. / Formula is =$K2=4	Abc
K4	2. / Formula is =$K2=1	Abc
K4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L4	1. / Formula is =$K2=4	Abc
L4	2. / Formula is =$K2=1	Abc
L4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M4	1. / Formula is =$K2=4	Abc
M4	2. / Formula is =$K2=1	Abc
M4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N4	1. / Formula is =$K2=4	Abc
N4	2. / Formula is =$K2=1	Abc
N4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O4	1. / Formula is =$K2=4	Abc
O4	2. / Formula is =$K2=1	Abc
O4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P4	1. / Formula is =$K2=4	Abc
P4	2. / Formula is =$K2=1	Abc
P4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q4	1. / Formula is =$K2=4	Abc
Q4	2. / Formula is =$K2=1	Abc
Q4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A5	1. / Formula is =$K2=4	Abc
A5	2. / Formula is =$K2=1	Abc
A5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B5	1. / Formula is =$K2=4	Abc
B5	2. / Formula is =$K2=1	Abc
B5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C5	1. / Formula is =$K2=4	Abc
C5	2. / Formula is =$K2=1	Abc
C5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D5	1. / Formula is =$K2=4	Abc
D5	2. / Formula is =$K2=1	Abc
D5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E5	1. / Formula is =$K2=4	Abc
E5	2. / Formula is =$K2=1	Abc
E5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F5	1. / Formula is =$K2=4	Abc
F5	2. / Formula is =$K2=1	Abc
F5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G5	1. / Formula is =$K2=4	Abc
G5	2. / Formula is =$K2=1	Abc
G5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H5	1. / Formula is =$K2=4	Abc
H5	2. / Formula is =$K2=1	Abc
H5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I5	1. / Formula is =$K2=4	Abc
I5	2. / Formula is =$K2=1	Abc
I5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J5	1. / Formula is =$K2=4	Abc
J5	2. / Formula is =$K2=1	Abc
J5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K5	1. / Formula is =$K2=4	Abc
K5	2. / Formula is =$K2=1	Abc
K5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L5	1. / Formula is =$K2=4	Abc
L5	2. / Formula is =$K2=1	Abc
L5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M5	1. / Formula is =$K2=4	Abc
M5	2. / Formula is =$K2=1	Abc
M5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N5	1. / Formula is =$K2=4	Abc
N5	2. / Formula is =$K2=1	Abc
N5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O5	1. / Formula is =$K2=4	Abc
O5	2. / Formula is =$K2=1	Abc
O5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P5	1. / Formula is =$K2=4	Abc
P5	2. / Formula is =$K2=1	Abc
P5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q5	1. / Formula is =$K2=4	Abc
Q5	2. / Formula is =$K2=1	Abc
Q5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A6	1. / Formula is =$K2=4	Abc
A6	2. / Formula is =$K2=1	Abc
A6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B6	1. / Formula is =$K2=4	Abc
B6	2. / Formula is =$K2=1	Abc
B6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C6	1. / Formula is =$K2=4	Abc
C6	2. / Formula is =$K2=1	Abc
C6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D6	1. / Formula is =$K2=4	Abc
D6	2. / Formula is =$K2=1	Abc
D6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E6	1. / Formula is =$K2=4	Abc
E6	2. / Formula is =$K2=1	Abc
E6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F6	1. / Formula is =$K2=4	Abc
F6	2. / Formula is =$K2=1	Abc
F6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G6	1. / Formula is =$K2=4	Abc
G6	2. / Formula is =$K2=1	Abc
G6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H6	1. / Formula is =$K2=4	Abc
H6	2. / Formula is =$K2=1	Abc
H6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I6	1. / Formula is =$K2=4	Abc
I6	2. / Formula is =$K2=1	Abc
I6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J6	1. / Formula is =$K2=4	Abc
J6	2. / Formula is =$K2=1	Abc
J6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K6	1. / Formula is =$K2=4	Abc
K6	2. / Formula is =$K2=1	Abc
K6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L6	1. / Formula is =$K2=4	Abc
L6	2. / Formula is =$K2=1	Abc
L6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M6	1. / Formula is =$K2=4	Abc
M6	2. / Formula is =$K2=1	Abc
M6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N6	1. / Formula is =$K2=4	Abc
N6	2. / Formula is =$K2=1	Abc
N6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O6	1. / Formula is =$K2=4	Abc
O6	2. / Formula is =$K2=1	Abc
O6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P6	1. / Formula is =$K2=4	Abc
P6	2. / Formula is =$K2=1	Abc
P6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q6	1. / Formula is =$K2=4	Abc
Q6	2. / Formula is =$K2=1	Abc
Q6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A7	1. / Formula is =$K2=4	Abc
A7	2. / Formula is =$K2=1	Abc
A7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B7	1. / Formula is =$K2=4	Abc
B7	2. / Formula is =$K2=1	Abc
B7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C7	1. / Formula is =$K2=4	Abc
C7	2. / Formula is =$K2=1	Abc
C7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D7	1. / Formula is =$K2=4	Abc
D7	2. / Formula is =$K2=1	Abc
D7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E7	1. / Formula is =$K2=4	Abc
E7	2. / Formula is =$K2=1	Abc
E7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F7	1. / Formula is =$K2=4	Abc
F7	2. / Formula is =$K2=1	Abc
F7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G7	1. / Formula is =$K2=4	Abc
G7	2. / Formula is =$K2=1	Abc
G7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H7	1. / Formula is =$K2=4	Abc
H7	2. / Formula is =$K2=1	Abc
H7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I7	1. / Formula is =$K2=4	Abc
I7	2. / Formula is =$K2=1	Abc
I7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J7	1. / Formula is =$K2=4	Abc
J7	2. / Formula is =$K2=1	Abc
J7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K7	1. / Formula is =$K2=4	Abc
K7	2. / Formula is =$K2=1	Abc
K7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L7	1. / Formula is =$K2=4	Abc
L7	2. / Formula is =$K2=1	Abc
L7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M7	1. / Formula is =$K2=4	Abc
M7	2. / Formula is =$K2=1	Abc
M7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N7	1. / Formula is =$K2=4	Abc
N7	2. / Formula is =$K2=1	Abc
N7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O7	1. / Formula is =$K2=4	Abc
O7	2. / Formula is =$K2=1	Abc
O7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P7	1. / Formula is =$K2=4	Abc
P7	2. / Formula is =$K2=1	Abc
P7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q7	1. / Formula is =$K2=4	Abc
Q7	2. / Formula is =$K2=1	Abc
Q7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2											0
3											1
4											2
5											3
6											4
7											5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =$K2=4	Abc
A2	2. / Formula is =$K2=1	Abc
A2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B2	1. / Formula is =$K2=4	Abc
B2	2. / Formula is =$K2=1	Abc
B2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C2	1. / Formula is =$K2=4	Abc
C2	2. / Formula is =$K2=1	Abc
C2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D2	1. / Formula is =$K2=4	Abc
D2	2. / Formula is =$K2=1	Abc
D2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E2	1. / Formula is =$K2=4	Abc
E2	2. / Formula is =$K2=1	Abc
E2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F2	1. / Formula is =$K2=4	Abc
F2	2. / Formula is =$K2=1	Abc
F2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G2	1. / Formula is =$K2=4	Abc
G2	2. / Formula is =$K2=1	Abc
G2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H2	1. / Formula is =$K2=4	Abc
H2	2. / Formula is =$K2=1	Abc
H2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I2	1. / Formula is =$K2=4	Abc
I2	2. / Formula is =$K2=1	Abc
I2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J2	1. / Formula is =$K2=4	Abc
J2	2. / Formula is =$K2=1	Abc
J2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K2	1. / Formula is =$K2=4	Abc
K2	2. / Formula is =$K2=1	Abc
K2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L2	1. / Formula is =$K2=4	Abc
L2	2. / Formula is =$K2=1	Abc
L2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M2	1. / Formula is =$K2=4	Abc
M2	2. / Formula is =$K2=1	Abc
M2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N2	1. / Formula is =$K2=4	Abc
N2	2. / Formula is =$K2=1	Abc
N2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O2	1. / Formula is =$K2=4	Abc
O2	2. / Formula is =$K2=1	Abc
O2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P2	1. / Formula is =$K2=4	Abc
P2	2. / Formula is =$K2=1	Abc
P2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q2	1. / Formula is =$K2=4	Abc
Q2	2. / Formula is =$K2=1	Abc
Q2	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A3	1. / Formula is =$K2=4	Abc
A3	2. / Formula is =$K2=1	Abc
A3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B3	1. / Formula is =$K2=4	Abc
B3	2. / Formula is =$K2=1	Abc
B3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C3	1. / Formula is =$K2=4	Abc
C3	2. / Formula is =$K2=1	Abc
C3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D3	1. / Formula is =$K2=4	Abc
D3	2. / Formula is =$K2=1	Abc
D3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E3	1. / Formula is =$K2=4	Abc
E3	2. / Formula is =$K2=1	Abc
E3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F3	1. / Formula is =$K2=4	Abc
F3	2. / Formula is =$K2=1	Abc
F3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G3	1. / Formula is =$K2=4	Abc
G3	2. / Formula is =$K2=1	Abc
G3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H3	1. / Formula is =$K2=4	Abc
H3	2. / Formula is =$K2=1	Abc
H3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I3	1. / Formula is =$K2=4	Abc
I3	2. / Formula is =$K2=1	Abc
I3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J3	1. / Formula is =$K2=4	Abc
J3	2. / Formula is =$K2=1	Abc
J3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K3	1. / Formula is =$K2=4	Abc
K3	2. / Formula is =$K2=1	Abc
K3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L3	1. / Formula is =$K2=4	Abc
L3	2. / Formula is =$K2=1	Abc
L3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M3	1. / Formula is =$K2=4	Abc
M3	2. / Formula is =$K2=1	Abc
M3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N3	1. / Formula is =$K2=4	Abc
N3	2. / Formula is =$K2=1	Abc
N3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O3	1. / Formula is =$K2=4	Abc
O3	2. / Formula is =$K2=1	Abc
O3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P3	1. / Formula is =$K2=4	Abc
P3	2. / Formula is =$K2=1	Abc
P3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q3	1. / Formula is =$K2=4	Abc
Q3	2. / Formula is =$K2=1	Abc
Q3	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A4	1. / Formula is =$K2=4	Abc
A4	2. / Formula is =$K2=1	Abc
A4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B4	1. / Formula is =$K2=4	Abc
B4	2. / Formula is =$K2=1	Abc
B4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C4	1. / Formula is =$K2=4	Abc
C4	2. / Formula is =$K2=1	Abc
C4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D4	1. / Formula is =$K2=4	Abc
D4	2. / Formula is =$K2=1	Abc
D4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E4	1. / Formula is =$K2=4	Abc
E4	2. / Formula is =$K2=1	Abc
E4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F4	1. / Formula is =$K2=4	Abc
F4	2. / Formula is =$K2=1	Abc
F4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G4	1. / Formula is =$K2=4	Abc
G4	2. / Formula is =$K2=1	Abc
G4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H4	1. / Formula is =$K2=4	Abc
H4	2. / Formula is =$K2=1	Abc
H4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I4	1. / Formula is =$K2=4	Abc
I4	2. / Formula is =$K2=1	Abc
I4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J4	1. / Formula is =$K2=4	Abc
J4	2. / Formula is =$K2=1	Abc
J4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K4	1. / Formula is =$K2=4	Abc
K4	2. / Formula is =$K2=1	Abc
K4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L4	1. / Formula is =$K2=4	Abc
L4	2. / Formula is =$K2=1	Abc
L4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M4	1. / Formula is =$K2=4	Abc
M4	2. / Formula is =$K2=1	Abc
M4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N4	1. / Formula is =$K2=4	Abc
N4	2. / Formula is =$K2=1	Abc
N4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O4	1. / Formula is =$K2=4	Abc
O4	2. / Formula is =$K2=1	Abc
O4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P4	1. / Formula is =$K2=4	Abc
P4	2. / Formula is =$K2=1	Abc
P4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q4	1. / Formula is =$K2=4	Abc
Q4	2. / Formula is =$K2=1	Abc
Q4	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A5	1. / Formula is =$K2=4	Abc
A5	2. / Formula is =$K2=1	Abc
A5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B5	1. / Formula is =$K2=4	Abc
B5	2. / Formula is =$K2=1	Abc
B5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C5	1. / Formula is =$K2=4	Abc
C5	2. / Formula is =$K2=1	Abc
C5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D5	1. / Formula is =$K2=4	Abc
D5	2. / Formula is =$K2=1	Abc
D5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E5	1. / Formula is =$K2=4	Abc
E5	2. / Formula is =$K2=1	Abc
E5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F5	1. / Formula is =$K2=4	Abc
F5	2. / Formula is =$K2=1	Abc
F5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G5	1. / Formula is =$K2=4	Abc
G5	2. / Formula is =$K2=1	Abc
G5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H5	1. / Formula is =$K2=4	Abc
H5	2. / Formula is =$K2=1	Abc
H5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I5	1. / Formula is =$K2=4	Abc
I5	2. / Formula is =$K2=1	Abc
I5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J5	1. / Formula is =$K2=4	Abc
J5	2. / Formula is =$K2=1	Abc
J5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K5	1. / Formula is =$K2=4	Abc
K5	2. / Formula is =$K2=1	Abc
K5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L5	1. / Formula is =$K2=4	Abc
L5	2. / Formula is =$K2=1	Abc
L5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M5	1. / Formula is =$K2=4	Abc
M5	2. / Formula is =$K2=1	Abc
M5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N5	1. / Formula is =$K2=4	Abc
N5	2. / Formula is =$K2=1	Abc
N5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O5	1. / Formula is =$K2=4	Abc
O5	2. / Formula is =$K2=1	Abc
O5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P5	1. / Formula is =$K2=4	Abc
P5	2. / Formula is =$K2=1	Abc
P5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q5	1. / Formula is =$K2=4	Abc
Q5	2. / Formula is =$K2=1	Abc
Q5	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A6	1. / Formula is =$K2=4	Abc
A6	2. / Formula is =$K2=1	Abc
A6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B6	1. / Formula is =$K2=4	Abc
B6	2. / Formula is =$K2=1	Abc
B6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C6	1. / Formula is =$K2=4	Abc
C6	2. / Formula is =$K2=1	Abc
C6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D6	1. / Formula is =$K2=4	Abc
D6	2. / Formula is =$K2=1	Abc
D6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E6	1. / Formula is =$K2=4	Abc
E6	2. / Formula is =$K2=1	Abc
E6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F6	1. / Formula is =$K2=4	Abc
F6	2. / Formula is =$K2=1	Abc
F6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G6	1. / Formula is =$K2=4	Abc
G6	2. / Formula is =$K2=1	Abc
G6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H6	1. / Formula is =$K2=4	Abc
H6	2. / Formula is =$K2=1	Abc
H6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I6	1. / Formula is =$K2=4	Abc
I6	2. / Formula is =$K2=1	Abc
I6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J6	1. / Formula is =$K2=4	Abc
J6	2. / Formula is =$K2=1	Abc
J6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K6	1. / Formula is =$K2=4	Abc
K6	2. / Formula is =$K2=1	Abc
K6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L6	1. / Formula is =$K2=4	Abc
L6	2. / Formula is =$K2=1	Abc
L6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M6	1. / Formula is =$K2=4	Abc
M6	2. / Formula is =$K2=1	Abc
M6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N6	1. / Formula is =$K2=4	Abc
N6	2. / Formula is =$K2=1	Abc
N6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O6	1. / Formula is =$K2=4	Abc
O6	2. / Formula is =$K2=1	Abc
O6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P6	1. / Formula is =$K2=4	Abc
P6	2. / Formula is =$K2=1	Abc
P6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q6	1. / Formula is =$K2=4	Abc
Q6	2. / Formula is =$K2=1	Abc
Q6	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
A7	1. / Formula is =$K2=4	Abc
A7	2. / Formula is =$K2=1	Abc
A7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
B7	1. / Formula is =$K2=4	Abc
B7	2. / Formula is =$K2=1	Abc
B7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
C7	1. / Formula is =$K2=4	Abc
C7	2. / Formula is =$K2=1	Abc
C7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
D7	1. / Formula is =$K2=4	Abc
D7	2. / Formula is =$K2=1	Abc
D7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
E7	1. / Formula is =$K2=4	Abc
E7	2. / Formula is =$K2=1	Abc
E7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
F7	1. / Formula is =$K2=4	Abc
F7	2. / Formula is =$K2=1	Abc
F7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
G7	1. / Formula is =$K2=4	Abc
G7	2. / Formula is =$K2=1	Abc
G7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
H7	1. / Formula is =$K2=4	Abc
H7	2. / Formula is =$K2=1	Abc
H7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
I7	1. / Formula is =$K2=4	Abc
I7	2. / Formula is =$K2=1	Abc
I7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
J7	1. / Formula is =$K2=4	Abc
J7	2. / Formula is =$K2=1	Abc
J7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
K7	1. / Formula is =$K2=4	Abc
K7	2. / Formula is =$K2=1	Abc
K7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
L7	1. / Formula is =$K2=4	Abc
L7	2. / Formula is =$K2=1	Abc
L7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
M7	1. / Formula is =$K2=4	Abc
M7	2. / Formula is =$K2=1	Abc
M7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
N7	1. / Formula is =$K2=4	Abc
N7	2. / Formula is =$K2=1	Abc
N7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
O7	1. / Formula is =$K2=4	Abc
O7	2. / Formula is =$K2=1	Abc
O7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
P7	1. / Formula is =$K2=4	Abc
P7	2. / Formula is =$K2=1	Abc
P7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc
Q7	1. / Formula is =$K2=4	Abc
Q7	2. / Formula is =$K2=1	Abc
Q7	3. / Formula is =AND($K2=0, $K2 <> "")	Abc

 

[etaf]

deleted
trying to reduce the conditional format formulas
and failed

 

[krispatterson]

Re: Conditional Formatting - easy help!!

your range is only row 3 is that what you want ?

for red
=AND($K3=0, $K3 <> "")

otherwise it looks OK, just change the range to include more cells

Thanks etaf, all works... have a great weekend

To clarify, and for ease, I just added in "-" to the rows that are blank but I don't want coloured! It's a basic workaround, but works exactly as I need so I'm happy!! :D