Conditional Formatting need to Highlight cell based on value in any order

J

JAVEDR

Board Regular
Joined
Sep 17, 2019
Messages
79
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Hello,

Conditional Formatting need to Highlight cell from B:D based on value in column A any order should match 3 digits of column A

Book3
ABCD
1237345678902456789023567890
2277345678902456789023567890
3479345678902456789023567890
4578345678902456789023567890
Sheet1


Thanks for your time and help
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hello JAVEDR,
May be exist some other way, but you can do it in this way... Create three rules with rule "Use a formula to determine..."
Apply rule to separeted ranges
First rule to range "=$B$1:$B$4", second to "=$C$1:$C$4", third to "=$D$1:$D$4".
Also for each rule use special formulas.
First rule "=AND(FIND(MID(A1;1;1);B1;1);FIND(MID(A1;2;1);B1;1);FIND(MID(A1;3;1);B1;1))"
Second rule"=AND(FIND(MID(A1;1;1);C1;1);FIND(MID(A1;2;1);C1;1);FIND(MID(A1;3;1);C1;1))"
Third rule "=AND(FIND(MID(A1;1;1);D1;1);FIND(MID(A1;2;1);D1;1);FIND(MID(A1;3;1);D1;1))"
 
Upvote 0
EXCEL MAX said:
Hello JAVEDR,
May be exist some other way, but you can do it in this way... Create three rules with rule "Use a formula to determine..."
Apply rule to separeted ranges
First rule to range "=$B$1:$B$4", second to "=$C$1:$C$4", third to "=$D$1:$D$4".
Also for each rule use special formulas.
First rule "=AND(FIND(MID(A1;1;1);B1;1);FIND(MID(A1;2;1);B1;1);FIND(MID(A1;3;1);B1;1))"
Second rule"=AND(FIND(MID(A1;1;1);C1;1);FIND(MID(A1;2;1);C1;1);FIND(MID(A1;3;1);C1;1))"
Third rule "=AND(FIND(MID(A1;1;1);D1;1);FIND(MID(A1;2;1);D1;1);FIND(MID(A1;3;1);D1;1))"
Click to expand...
thank you sir for your reply... but there are almost more than 45 columns which highlight based on A1 formula is bit complicated to apply on all columns
 
Upvote 0
Select all Rage from Cell B1 to Last Cell at Last Column (e.g. Z45)
Create One rules with rule "Use a formula to determine..."
Excel Formula: 
=AND(FIND(MID($A1;1;1);B1;1);FIND(MID($A1;2;1);B1;1);FIND(MID($A1;3;1);B1;1))
And Add Your conditional Format And APPLY
Again Check Excel Don't Add More $ sign to Your formula at conditional Formatting
 
Upvote 0
maabadi said:
Select all Rage from Cell B1 to Last Cell at Last Column (e.g. Z45)
Create One rules with rule "Use a formula to determine..."
Excel Formula: 
=AND(FIND(MID($A1;1;1);B1;1);FIND(MID($A1;2;1);B1;1);FIND(MID($A1;3;1);B1;1))
And Add Your conditional Format And APPLY
Again Check Excel Don't Add More $ sign to Your formula at conditional Formatting
Click to expand...
Thank you for your reply.

I have tried above formula on range from B1:AT1 none of cell highlights
 
Upvote 0
How about this!

TempO.xlsx
ABCD
1237345678902456789023567890
2277345678902456789023567890
3479345678902456789023567890
4578345678902456789023567890
5224245678902426789023567890
6777127555551722700127557170
7
14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:D6Expression=AND(SEARCH(LEFT($A1,1),B1)>0,IF(LEFT($A1,1)=MID($A1,2,1),SEARCH(MID($A1,2,1),B1,SEARCH(MID($A1,2,1),$A1)+1),SEARCH(MID($A1,2,1),B1)>0),SEARCH(RIGHT($A1,1),IF(SUM(--(RIGHT($A1,1)=LEFT($A1,1)),--(RIGHT($A1,1)=MID($A1,2,1)))=0,B1,IF(SUM(--(RIGHT($A1,1)=LEFT($A1,1)),--(RIGHT($A1,1)=MID($A1,2,1)))=1,SUBSTITUTE(B1,RIGHT($A1,1),"",1),SUBSTITUTE(SUBSTITUTE(B1,RIGHT($A1,1),"",1),RIGHT($A1,1),"",1))))>0)textNO
 
Upvote 0
alz said:
How about this!

TempO.xlsx
ABCD
1237345678902456789023567890
2277345678902456789023567890
3479345678902456789023567890
4578345678902456789023567890
5224245678902426789023567890
6777127555551722700127557170
7
14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:D6Expression=AND(SEARCH(LEFT($A1,1),B1)>0,IF(LEFT($A1,1)=MID($A1,2,1),SEARCH(MID($A1,2,1),B1,SEARCH(MID($A1,2,1),$A1)+1),SEARCH(MID($A1,2,1),B1)>0),SEARCH(RIGHT($A1,1),IF(SUM(--(RIGHT($A1,1)=LEFT($A1,1)),--(RIGHT($A1,1)=MID($A1,2,1)))=0,B1,IF(SUM(--(RIGHT($A1,1)=LEFT($A1,1)),--(RIGHT($A1,1)=MID($A1,2,1)))=1,SUBSTITUTE(B1,RIGHT($A1,1),"",1),SUBSTITUTE(SUBSTITUTE(B1,RIGHT($A1,1),"",1),RIGHT($A1,1),"",1))))>0)textNO
Click to expand...
1611306178588.png

i think doing wrong as above formula i pasted here nothing highlight
 
Upvote 0
You have to select format color! you are not select yet
 

Attachments

  • Screenshot (270).png
    Screenshot (270).png
    17.5 KB · Views: 7
Upvote 0
alz said:
You have to select format color! you are not select yet
Click to expand...
its working but my range is like below and you can see for 224 cell are getting higlight which i dont want. I want 3unique digit should match numbers should match
and from B4:B6 nothing highights

Book6
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1
2
3
4345345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
5277345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
6479345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
7578345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
8224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
9345345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
10277345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
11479345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
12578345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
13224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
14345345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
15277345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
16479345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
17578345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
18224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
19345345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
20277345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
21479345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
22578345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
23345345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
24224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
25224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
26224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
27224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
28224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
29224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
30224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
31224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
32224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
33224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
34224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
35224345678902456789023567890234678902345789023456890234567902345678023456789145678901356789013467890134578901345689013456790134567801345678912567890124678901245789012456890124567901245678012456789123678901235789012356890123567901235678012356789123478901234689012346790123467801234678912345890123457901234578012345789123456901234568012345689123456701234567912345678
36
37
38
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:AU35Expression=AND(SEARCH(LEFT($B4,1),B1)>0,IF(LEFT($B4,1)=MID($B4,2,1),SEARCH(MID($B4,2,1),C4,SEARCH(MID($B4,2,1),$B4)+1),SEARCH(MID($B4,2,1),C4)>0),SEARCH(RIGHT($B4,1),IF(SUM(--(RIGHT($B4,1)=LEFT($B4,1)),--(RIGHT($B4,1)=MID($B4,2,1)))=0,B1,IF(SUM(--(RIGHT($B4,1)=LEFT($B4,1)),--(RIGHT($B4,1)=MID($B4,2,1)))=1,SUBSTITUTE(B1,RIGHT($B4,1),"",1),SUBSTITUTE(SUBSTITUTE(B1,RIGHT($B4,1),"",1),RIGHT($B4,1),"",1))))>0)textYES
 
Upvote 0
I think Maabadi's formula works,
but you did not insert copy correctly.
Check each letter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Excelwhatsthat
  • Question Question
Scroll enabled to minimise table view...but need conditional formatting
Replies
0
Views
79
Excelwhatsthat
Excelwhatsthat
M
  • Question Question
3 columns - Highlight lowest value - But only if true lowest not a tie.
Replies
5
Views
56
dreid1011
dreid1011
S
  • Question Question
Help with conditional formatting using a pivot table with dynamic ranges
Replies
1
Views
96
RoryA
RoryA
E
  • Question Question
Specific VBA Function
Replies
0
Views
252
EAlexWolfe
E
E
  • Question Question
How to Highlight Each Column-Heading of the Active Row When The Corresponding Cells Contain 'X'
Replies
2
Views
68
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

Share this page

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