Conditional Formatting

[blovell91]

Hi All,

I feel my question is rather easy but I'm making a total mess of it so far.

Basically, I want to check the value of Column I, and depending on the text inside the cell, change it's colour (either red, orange or green).

The column will contain either C1, C2, C3, ---- C74.

And for example, C23, C26, C30 and C31 need to be red.

Is there a way to write a formula that will check for multiple values (as mentioned above) and change the colour? Surely I do not need to write 74 individual ones for each reference, but can group all of the red values together, and the orange together etc.

I don't know if i'm using equal to, text contains, or some kind of true or false and struggling to find the answer online.

Any help will be greatly appreciated.

Thanks

 

[etaf]

yes , you should be able to
Why are they red?
And for example, C23, C26, C30 and C31 need to be red.

Is it because the text in the Cell is "C23" etc
OR are you referring to Cell references Column C row 23 ??

you should able to use an OR formula in conditional formatting

Select Column I
$I:$I

Then for RED a red fill

=OR( I1="C23", I1="C26", I1="C30", I1="C31")

Can you give all
the red conditions
the orange conditions
and
the green conditions
for C1 to C74
in case there is also a pattern to the text and colour

 

[blovell91]

yes , you should be able to
Why are they red?
And for example, C23, C26, C30 and C31 need to be red.

Is it because the text in the Cell is "C23" etc
OR are you referring to Cell references Column C row 23 ??

you should able to use an OR formula in conditional formatting

Select Column I
$I:$I

Then for RED a red fill

=OR( I1="C23", I1="C26", I1="C30", I1="C31")

Can you give all
the red conditions
the orange conditions
and
the green conditions
for C1 to C74
in case there is also a pattern to the text and colour

Hello!

Yeah I wish they hadn't started referencing using "C[insert number], it' making things more confusing.

This is checking for the text, not the cell C23 etc.

Ok so the breakdown is as follows

Green
C1 C6 C7 C25 C34 C35 C36 C37 C38 C41 C43 C44 C45 C46 C47 C48 C49 C50 C55 C56 C57 C58 C59 C61 C62 C63 C64 C72

Orange/Amber
C2 C3 C4 C5 C8 C9 C10 C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22 C23 C24 C27 C28 C29 C30 C39 C42 C51 C54 C60 C67 C68 C69 C70 C71 C73 C74 C75 C76

Red
C23 C26 C31 C32 C33 C40 C52 C53 C65 C66

Also, when I highlight column I and then click conditional formatting, it is asking the following

 

[etaf]

you could use either a lookup list , so if anything changes you just update the list , using a countif(List for red,I1)>0


for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting

Highlight applicable range >>

I:I


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=COUNTIF(REF!$A:$A,I1)>0


FormatÖ [Number, Font, Border, Fill] Green
choose the format you would like to apply when the condition is true
OK >> OK

=COUNTIF(REF!$A:$A,I1)>0
=COUNTIF(REF!$B:$B,I1)>0
=COUNTIF(REF!$C:$C,I1)>0


I have added the list onto a sheet named REF

Book5
	F	G	H	I	J
1				c1
2				c4
3				c6
4				c2
5				c7
6				c1
7				c4
8				c6
9				c2
10				C8
11				C9
12				C10
13				C12
14				C33
15				C40
16				C52
17				C53
18				c53
19				C34
20				C35
21				C36
22				C37
23				C8
24				C9
25				C10
26				C12
27				c33
28				C40
29				C52
30				C53
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Expression	=COUNTIF(REF!$A:$A,I1)>0	text	NO
I:I	Expression	=COUNTIF(REF!$B:$B,I1)>0	text	NO
I:I	Expression	=COUNTIF(REF!$C:$C,I1)>0	text	NO

Book5
	A	B	C
1	GREEN	Orange	RED
2	C1	C2	C23
3	C6	C3	C26
4	C7	C4	C31
5	C25	C5	C32
6	C34	C8	C33
7	C35	C9	C40
8	C36	C10	C52
9	C37	C12	C53
10	C38	C13	C65
11	C41	C14	C66
12	C43	C15
13	C44	C16
14	C45	C17
15	C46	C18
16	C47	C19
17	C48	C20
18	C49	C21
19	C50	C22
20	C55	C23
21	C56	C24
22	C57	C27
23	C58	C28
24	C59	C29
25	C61	C30
26	C62	C39
27	C63	C42
28	C64	C51
29	C72	C54
30		C60
31		C67
32		C68
33		C69
34		C70
35		C71
36		C73
37		C74
38		C75
39		C76
REF

 

[jasonb75]

Slightly different approach that should reduce (but not completely eliminate) the possibility of errors in the reference list (C23 is listed in Red and Orange).

The List.xlsx
	C	D	E
1	Green		C11
2	Orange		C16
3	Orange		C6
4	Orange		C18
5	Red
6	Red
7	Red
8	Red
9	Orange
10	Orange
11	Orange
12	Orange
13	Green
14	Green
15	Green
16	Green
17	Green
Ref

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1:E4	Expression	=INDIRECT("Ref!"&E1)="Red"	text	NO
E1:E4	Expression	=INDIRECT("Ref!"&E1)="Orange"	text	NO
E1:E4	Expression	=INDIRECT("Ref!"&E1)="Green"	text	NO

 

[etaf]

@jasonb75
Clever solution, learned something new again, cheers