Conditional formatting ? numbers match highlight?

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,072
Office Version
  1. 365
Platform
  1. Windows
Hi I have on one sheet column M to R. If number in these cells match the same numbers from C to G. Then highlight those cells that match from C to G.

So lets example if 5 is in M4 and 5 is then in D5. Highlight D5 any help thanks
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
yeah but I think I need the whole range. lets say the number 20 was in P12 and the number 20 was in C14, E15, F22 AND G18 . Then all these should highlight C14, E15, F22 AND G18. Since 20 is in P12. Basically my range is in any number from colum M to R matches any number from column C to G. To then highlight those numbers only from C to G
 
Upvote 0
OK, well that may need VBA, with which I can't help you - sorry. It would be so much better to tell us what you really want from the outset. :)
 
Upvote 0
ok I posted an image of what I'm looking to do, but I think this can be done using conditional Formatting. Trying to research it more.

Excel 2016 (Windows) 32 bit
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
3
Rick
25
8
9
32
32
4
5
12
20
25
30
4
Frank
4
5
6
2​
2​
7
5
12
22
7
30
5
Leo
6
1
7
2​
2​
4
21
12
20
8
30
6
Maria
3
21
8
5​
5​
4
5
12
20
9
30
7
Mary
32
12
9
6​
6​
4
5
12
20
25
30
Sheet: Sheet1
<strike></strike>
 
Upvote 0
Try highlighting C3:G7 (or however far down you need), Conditional Formatting, New Rule, Use a formula

=COUNTIFS($M:$R,C3,$M:$R,"<>")

Format yellow fill

Edit: Shouldn't D3, E3, E6, D7, and E7 be highlighted too? Those numbers are found in columns M:R.
 
Last edited:
Upvote 0
BOOM it works. Sorry yes they should I probably missed it when highlighting manually. Thanks for the help happy I can use conditional formatting for this. Big help
 
Upvote 0
BOOM it works. Sorry yes they should I probably missed it when highlighting manually. Thanks for the help happy I can use conditional formatting for this. Big help

You're welcome. Glad to help. The example in post #5 greatly helped clear things up.
 
Upvote 0
Sorry wait I think I'm missing a little something. I see on the left that some numbers are not highlighting. Think a formual issue? If you have another sec thanks

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
2
#
#
#
#
#
#
3
Rick
25
8
9
32
32
4
5
12
20
25
30
4
Frank
4
5
6
2​
2​
7
5
12
22
7
30
5
Leo
6
1
7
2​
2​
4
21
12
20
8
30
6
Maria
3
21
8
5​
5​
5
5
12
20
9
30
7
Mary
32
12
9
6​
6​
4
5
12
20
25
30
8
Mark
2
12
10
9​
9​
25
5
12
20
30
30
9
Lou Nuts
16
20
30
6​
6​
4
5
12
12
25
35
10
Frank
4
5
6
63​
5​
25
5
12
20
25
25
11
Leo
6
1
7
45​
45​
7
12
12
20
25
30
12
Maria
3
21
8
12​
12​
20
5
12
20
45
30
13
Mary
32
12
9
45​
45​
4
5
12
15
25
30
14
Mark
2
12
10
12​
12​
4
25
12
20
25
30
15
Lou Nuts
16
20
30
20​
20​
4
5
12
20
23
30
16
Frank
25
6
8
23​
25​
12
5
12
20
25
50
17
Leo
6
7
9
26​
26​
4
5
12
20
25
56
18
Maria
3
8
10
2​
2​
4
5
12
20
25
25
19
Mary
32
9
30
5​
5​
4
5
12
21
25
21
20
Mark
2
10
6
12​
12​
4
5
12
20
25
20
21
Lou Nuts
16
30
7
56​
56​
4
5
12
22
25
21
22
Frank
2
10
8
25​
25​
25
5
12
23
25
21
23
Leo
16
30
9
30​
30​
4
5
12
20
25
30

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

Like I see 6 highlight but no 6 on right. Then I see 25 on left not highlighted but 25 is on right. Why countif? maybe match just matching numbers
 
Last edited:
Upvote 0
I think that you applied the Conditional Formatting incorrectly. Highlight C3:G23 and then follow the steps outlined in post #6.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top