Formula for conditional formatting

Kishan · Nov 4, 2019

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Here the below is my example I need formula for conditional format to highlight result in 3 different forms highlight 2 match, 3 match or 4, for example if I put in the cell C3=1 & in the D3=2 it should highlight in the column C6:D44 all 2 matches 1&2 and same way for 3 match in the columns H, I, J & 4 match in the columns M, N, O, P</SPAN></SPAN>

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

2

3

1

2

1

2

X

1

2

X

4

5

P1

P2

P3

P4

P1

P2

P3

P4

P1

P2

P3

P4

6

X

1

X

1

X

1

7

X

2

1

2

X

1

X

2

1

8

X

1

2

X

1

2

1

2

X

9

1

10

X

1

X

1

X

1

11

1

2

X

1

2

X

1

12

X

1

X

2

X

1

X

2

X

1

X

2

13

1

2

X

1

2

X

2

1

2

X

1

14

1

X

2

1

X

2

1

X

2

15

X

2

1

X

2

1

X

2

1

16

1

17

1

18

1

2

X

1

19

2

X

2

1

2

X

1

2

X

2

1

20

1

2

X

1

2

X

1

2

X

21

1

2

1

2

X

2

1

2

22

X

2

1

X

1

2

X

2

1

X

23

1

X

1

2

X

1

X

24

X

2

1

2

X

1

X

2

1

25

1

X

1

2

X

1

X

1

26

1

2

1

2

X

2

1

2

27

X

2

1

X

2

1

X

2

1

28

X

1

2

X

1

2

1

2

X

29

1

30

X

2

1

X

2

1

X

2

1

31

X

1

X

2

X

1

X

2

X

1

X

2

32

2

X

2

1

2

X

2

1

2

X

2

1

33

1

2

X

1

2

X

1

34

1

2

1

2

1

2

35

1

2

1

2

1

2

1

36

2

1

X

1

2

1

X

1

2

1

X

1

37

1

2

X

38

1

X

1

X

1

2

X

39

X

1

2

1

X

1

2

1

X

1

2

1

40

1

2

X

1

X

1

X

1

41

X

1

X

1

X

1

42

1

X

1

X

1

X

1

43

1

2

1

2

X

1

2

1

44

X

2

1

X

2

1

X

2

1

45

46

47

48

Sheet10

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

jasonb75 · Nov 4, 2019

Here's the first one for you, apply the cf rule to $C$6:$C$44 or your actual range if larger.

=AND(C$3<>"",OR($C6=$C$3,$C$3=""),OR($D6=$D$3,$D$3=""),OR($E6=$E$3,$E$3=""),OR($F6=$F$3,$F$3=""))

Change the columns in the formula as needed for the other 2 sets.

Peter_SSs · Nov 4, 2019

I've done 2 for what you appear to want. You should be able to extend to the third section.

<b>CF</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:56px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:center; ">P1</td><td style="font-size:10pt; text-align:center; ">P2</td><td style="font-size:10pt; text-align:center; ">P3</td><td style="font-size:10pt; text-align:center; ">P4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">P1</td><td style="font-size:10pt; text-align:center; ">P2</td><td style="font-size:10pt; text-align:center; ">P3</td><td style="font-size:10pt; text-align:center; ">P4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; "> </td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">1</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">2</td><td style="background-color:#ffc000; font-size:10pt; text-align:center; ">X</td><td style="font-size:10pt; text-align:center; ">X</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C6</td><td >1. / Formula is =$C6&$D6=$C$3&$D$3</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >D6</td><td >1. / Formula is =$C6&$D6=$C$3&$D$3</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >H6</td><td >1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >I6</td><td >1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >J6</td><td >1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3</td><td style="background-color:#ffc000; ">Abc</td></tr></table></td></tr></table>

Kishan · Nov 4, 2019

jasonb75 said:
Here's the first one for you, apply the cf rule to $C$6:$C$44 or your actual range if larger.

=AND(C$3<>"",OR($C6=$C$3,$C$3=""),OR($D6=$D$3,$D$3=""),OR($E6=$E$3,$E$3=""),OR($F6=$F$3,$F$3=""))

Change the columns in the formula as needed for the other 2 sets.

jasonb75, as you explain your formula worked fine, but it is more interesting when I applied formula selecting cell C6:F44, it highlights 1 in the column if I put 1 in C3, then if I put 2 in the D3 it highlight 2 in the column D and disappear extra 1's from column C and show only 1&2 highlighted cells and when keep extending E3 & F3 does the same so far "your single formula is doing multiple job highlights any 1, 2, 3 or 4 as required" </SPAN></SPAN>I like the formula </SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan

</SPAN></SPAN>

Kishan · Nov 4, 2019

Peter_SSs said:
I've done 2 for what you appear to want. You should be able to extend to the third section.

Conditional formatting

Cell
Nr.: / Condition
Format

C6
1. / Formula is =$C6&$D6=$C$3&$D$3

<TBODY>
[TD="bgcolor: #ffc000"]Abc

<TBODY>

D6
1. / Formula is =$C6&$D6=$C$3&$D$3

H6
1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3

I6
1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3

J6
1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3

[TD="bgcolor: #ffc000"]Abc
[/TD]

[TD="bgcolor: #ffc000"]Abc
[/TD]

[TD="bgcolor: #ffc000"]Abc
[/TD]

[TD="bgcolor: #ffc000"]Abc
[/TD]

</TBODY>

[/TD]

</TBODY>

Peter_SSs, formulas are working perfect for both the options as you provide, and for 3rd one it was easy I just change the range and added one more column it worked fine!</SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan

</SPAN></SPAN>

Peter_SSs · Nov 4, 2019

Kishan said:
Peter_SSs, formulas are working perfect for both the options as you provide, and for 3rd one it was easy I just change the range and added one more column it worked fine!</SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>

You're welcome. Thanks for the follow-up.

Formula for conditional formatting

Kishan

Well-known Member

Excel Facts

jasonb75

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page


D6	1. / Formula is =$C6&$D6=$C$3&$D$3
H6	1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3
I6	1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3
J6	1. / Formula is =$H6&$I6&$J6=$H$3&$I$3&$J$3

Formula for conditional formatting

Kishan

Well-known Member

Excel Facts

jasonb75

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock