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
 
Sort by date Sort by votes

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
sir, below error im getting


maabadi said:
Check again my formula with adding colors.
Click to expand...
1611307229800.png
 
Upvote 0
JAVEDR said:
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
Click to expand...

For this example: Use this
=AND(SEARCH(LEFT($B4,1),C4)>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,C4,IF(SUM(--(RIGHT($B4,1)=LEFT($B4,1)),--(RIGHT($B4,1)=MID($B4,2,1)))=1,SUBSTITUTE(C4,RIGHT($B4,1),"",1),SUBSTITUTE(SUBSTITUTE(C4,RIGHT($B4,1),"",1),RIGHT($B4,1),"",1))))>0)

you need to know how to adjust the formula about absolute and relative reference !
 
Upvote 0
1. go to formula and delete = at the first of formula. and Apply
2. if you don't See colored cells you should Change ; to Comma ,
3. Check again formula don't change at Conditional formatting.
 
Upvote 0
alz said:
For this example: Use this
=AND(SEARCH(LEFT($B4,1),C4)>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,C4,IF(SUM(--(RIGHT($B4,1)=LEFT($B4,1)),--(RIGHT($B4,1)=MID($B4,2,1)))=1,SUBSTITUTE(C4,RIGHT($B4,1),"",1),SUBSTITUTE(SUBSTITUTE(C4,RIGHT($B4,1),"",1),RIGHT($B4,1),"",1))))>0)

you need to know how to adjust the formula about absolute and relative reference !
Click to expand...

I guess it works perfect will confirm applying for 2.3 days thanks a ton for you help.

thank you everyone for giving you time.
 
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
69
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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