Conditional formatting using Search function

W

WendyHubard

New Member
Joined
Apr 4, 2017
Messages
39
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello Guru's
I'm trying to us conditional formatting on a table based on values enter in cell C1 and using the search function. It sort of works, but if C1 is empty/blank, the format is applied to all cells. Any Clues. Have had to use screen shots due to work security, but happy to upload a modified spreadsheet. Have tried with and without the first Formula. Any help greatly appreciated.

1742520426066.png


CF:
1742520361023.png



1742520305727.png


IF C! is BLANK
1742520478565.png
 
Sort by date Sort by votes
You haven't actually told us what you want to do.

Are you wanting to match $C$1 only on the values in column C? Because the formula you showed checks every column, but H, I, and J will never contain that value. I am guessing from context that you want to highlight every cell in the row if the value in column C of that row matches the value in C1. And C1 is covered up, but it looks like the value 15 in your shape is what is in C1. And are you looking for 15 anywhere in the cell, or an exact match? SEARCH will find 15 in 215. Again, from context it looks like you want an exact match.

I am guessing you want this but need more detail to be sure.

Excel Formula: 
=AND(NOT(ISBLANK($A1)),$B4=$C$1,$C$1<>"", $D$4<>"")
 
Upvote 0
Thanks Jeff,
Sorry it's so muddled. I did try the formula but nothing is highlighted. YES the value is in C1 - was just trying to make it easier for end user to know to put the search options. Am I able to upload a sample version?
 
Upvote 0
maybe this? It drove me nuts til I tried a helper column.

Book1
ABCDEFGHIJK
1Search89
2
3Binder NameIndex NumberPurchzsprOrder NumberUniquePurchase DateShop Complaintnon Shop ComplaintOriginComments from SAFEHelper
4202057Buyer 90OrdNum 28456Unique 92679610/2/2024TRUEFALSEFALSE
520232Buyer 48OrdNum 77550Unique 4518073/20/2022FALSETRUEFALSE
6201913Buyer 21OrdNum 60514Unique 2915938/23/2020FALSETRUEFALSE
720209Buyer 41OrdNum 35296Unique 83922512/1/2022TRUEFALSEFALSE
820207Buyer 74OrdNum 46456Unique 1367419/29/2020TRUEFALSEFALSE
9202526Buyer 77OrdNum 51646Unique 3212161/31/2021TRUEFALSEFALSE
10201912Buyer 83OrdNum 31622Unique 5240891/31/2025TRUEFALSEFALSE
11202361Buyer 74OrdNum 49468Unique 3920208/24/2019FALSETRUEFALSE
12202017Buyer 48OrdNum 10647Unique 6652872/27/2025TRUEFALSEFALSE
13202213Buyer 38OrdNum 73867Unique 9610255/1/2019TRUEFALSEFALSE
14202480Buyer 34OrdNum 13682Unique 6264399/24/2024TRUEFALSEFALSE
15202589Buyer 16OrdNum 47794Unique 21109011/5/2024FALSETRUETRUE
16201948Buyer 11OrdNum 39396Unique 55079710/31/2024TRUEFALSEFALSE
17202529Buyer 73OrdNum 77354Unique 2139191/10/2024FALSETRUEFALSE
18202078Buyer 20OrdNum 30620Unique 40320012/31/2022TRUEFALSEFALSE
19201939Buyer 38OrdNum 20470Unique 9951119/30/2019FALSETRUEFALSE
20202170Buyer 80OrdNum 83981Unique 4782099/9/2023FALSETRUEFALSE
21202051Buyer 87OrdNum 89880Unique 8458958/7/2020FALSETRUEFALSE
22202443Buyer 2OrdNum 73770Unique 7565032/21/2024FALSETRUEFALSE
23202593Buyer 70OrdNum 48773Unique 9229512/15/2019TRUEFALSEFALSE
Sheet1
Cell Formulas
RangeFormula
K4:K23K4=B4=$C$1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:J23Expression=$K4=TRUEtextNO
 
Upvote 0
HHmm... not sure. I was trying to use the SEARCH function so the end user could type a name, order name, purchaser etc. Think the formula will only look at values entered in C1 and match with Column B.
 
Upvote 0
Book2
ABCDEFGHIJKL
1Search
2
3Binder NameIndex NumberPurchzsprOrder NumberUniquePurchase DateShop Complaintnon Shop ComplaintOriginComments from SAFEIDHelper
4202057Buyer 90OrdNum 28456Unique 92679645567TRUEFALSE202057Buyer 90OrdNum 28456Unique 926796 
520232Buyer 48OrdNum 77550Unique 45180744640FALSETRUE20232Buyer 48OrdNum 77550Unique 451807
6201913Buyer 21OrdNum 60514Unique 29159344066FALSETRUE201913Buyer 21OrdNum 60514Unique 291593
720209Buyer 41OrdNum 35296Unique 83922544896TRUEFALSE20209Buyer 41OrdNum 35296Unique 839225
820207Buyer 74OrdNum 46456Unique 13674144103TRUEFALSE20207Buyer 74OrdNum 46456Unique 136741
9202526Buyer 77OrdNum 51646Unique 32121644227TRUEFALSE202526Buyer 77OrdNum 51646Unique 321216
10201912Buyer 83OrdNum 31622Unique 52408945688TRUEFALSE201912Buyer 83OrdNum 31622Unique 524089
11202361Buyer 74OrdNum 49468Unique 39202043701FALSETRUE202361Buyer 74OrdNum 49468Unique 392020
12202017Buyer 48OrdNum 10647Unique 66528745715TRUEFALSE202017Buyer 48OrdNum 10647Unique 665287
13202213Buyer 38OrdNum 73867Unique 96102543586TRUEFALSE202213Buyer 38OrdNum 73867Unique 961025
14202480Buyer 34OrdNum 13682Unique 62643945559TRUEFALSE202480Buyer 34OrdNum 13682Unique 626439
15202589Buyer 16OrdNum 47794Unique 21109045601FALSETRUE202589Buyer 16OrdNum 47794Unique 211090
16201948Buyer 11OrdNum 39396Unique 55079745596TRUEFALSE201948Buyer 11OrdNum 39396Unique 550797
17202529Buyer 73OrdNum 77354Unique 21391945301FALSETRUE202529Buyer 73OrdNum 77354Unique 213919
18202078Buyer 20OrdNum 30620Unique 40320044926TRUEFALSE202078Buyer 20OrdNum 30620Unique 403200
19201939Buyer 38OrdNum 20470Unique 99511143738FALSETRUE201939Buyer 38OrdNum 20470Unique 995111
20202170Buyer 80OrdNum 83981Unique 47820945178FALSETRUE202170Buyer 80OrdNum 83981Unique 478209
21202051Buyer 87OrdNum 89880Unique 84589544050FALSETRUE202051Buyer 87OrdNum 89880Unique 845895
22202443Buyer 2OrdNum 73770Unique 75650345343FALSETRUE202443Buyer 2OrdNum 73770Unique 756503
23202593Buyer 70OrdNum 48773Unique 92295143511TRUEFALSE202593Buyer 70OrdNum 48773Unique 922951
Sheet1
Cell Formulas
RangeFormula
L4L4=IF($C$1="","",NOT(ISERROR(SEARCH("*"&$C$1,$K$4:$K$23))))
K4:K23K4=A4&B4&C4&D4&E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:J23Expression=$L4=TRUEtextNO
 
Upvote 0
Solution
WendyHubard said:
That worked a treat!!!
Click to expand...
Are you sure?
WendyHubard said:
user could type a name, order name, purchaser etc.
Click to expand...
Does that statement above include say an Index Number or Binder Name to search on? If so, you could get some strange results.
Example 1. Search for Index Number 13

25 03 22.xlsm
ABCDEKL
1Search13
2
3Binder NameIndex NumberPurchzsprOrder NumberUniqueID
4202057Buyer 90OrdNum 28456Unique 926796202057Buyer 90OrdNum 28456Unique 926796FALSE
520232Buyer 48OrdNum 77550Unique 45180720232Buyer 48OrdNum 77550Unique 451807FALSE
6201913Buyer 21OrdNum 60514Unique 291593201913Buyer 21OrdNum 60514Unique 291593TRUE
720209Buyer 41OrdNum 35296Unique 83922520209Buyer 41OrdNum 35296Unique 839225FALSE
820207Buyer 74OrdNum 46456Unique 13674120207Buyer 74OrdNum 46456Unique 136741TRUE
9202526Buyer 77OrdNum 51646Unique 321216202526Buyer 77OrdNum 51646Unique 321216FALSE
10201912Buyer 83OrdNum 31622Unique 524089201912Buyer 83OrdNum 31622Unique 524089FALSE
11202361Buyer 74OrdNum 42023Unique 392020202361Buyer 74OrdNum 42023Unique 392020FALSE
12202017Buyer 48OrdNum 10647Unique 665287202017Buyer 48OrdNum 10647Unique 665287FALSE
13202213Buyer 38OrdNum 73867Unique 961025202213Buyer 38OrdNum 73867Unique 961025TRUE
14202480Buyer 34OrdNum 13682Unique 626439202480Buyer 34OrdNum 13682Unique 626439TRUE
15202589Buyer 16OrdNum 47794Unique 211090202589Buyer 16OrdNum 47794Unique 211090FALSE
16201948Buyer 11OrdNum 39396Unique 550797201948Buyer 11OrdNum 39396Unique 550797FALSE
17202529Buyer 73OrdNum 77354Unique 213919202529Buyer 73OrdNum 77354Unique 213919TRUE
18202078Buyer 20OrdNum 30620Unique 403200202078Buyer 20OrdNum 30620Unique 403200FALSE
19201939Buyer 38OrdNum 20470Unique 995111201939Buyer 38OrdNum 20470Unique 995111FALSE
20202170Buyer 80OrdNum 83981Unique 478209202170Buyer 80OrdNum 83981Unique 478209FALSE
21202051Buyer 87OrdNum 89880Unique 845895202051Buyer 87OrdNum 89880Unique 845895FALSE
22202443Buyer 2OrdNum 73770Unique 756503202443Buyer 2OrdNum 73770Unique 756503FALSE
23202593Buyer 70OrdNum 48773Unique 922951202593Buyer 70OrdNum 48773Unique 922951FALSE
CF (2)
Cell Formulas
RangeFormula
L4:L23L4=IF($C$1="","",NOT(ISERROR(SEARCH("*"&$C$1,$K$4:$K$23))))
K4:K23K4=A4&B4&C4&D4&E4
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:J23Expression=$L4=TRUEtextNO


Example 2. Search for Binder Name 2023

25 03 22.xlsm
ABCDEKL
1Search2023
2
3Binder NameIndex NumberPurchzsprOrder NumberUniqueID
4202057Buyer 90OrdNum 28456Unique 926796202057Buyer 90OrdNum 28456Unique 926796FALSE
520232Buyer 48OrdNum 77550Unique 45180720232Buyer 48OrdNum 77550Unique 451807TRUE
6201913Buyer 21OrdNum 60514Unique 291593201913Buyer 21OrdNum 60514Unique 291593FALSE
720209Buyer 41OrdNum 35296Unique 83922520209Buyer 41OrdNum 35296Unique 839225FALSE
820207Buyer 74OrdNum 42023Unique 13674120207Buyer 74OrdNum 42023Unique 136741TRUE
9202526Buyer 77OrdNum 51646Unique 321216202526Buyer 77OrdNum 51646Unique 321216FALSE
10201912Buyer 83OrdNum 31622Unique 524089201912Buyer 83OrdNum 31622Unique 524089FALSE
11202361Buyer 74OrdNum 49468Unique 392020202361Buyer 74OrdNum 49468Unique 392020TRUE
12202017Buyer 48OrdNum 10647Unique 665287202017Buyer 48OrdNum 10647Unique 665287FALSE
CF (2)
Cell Formulas
RangeFormula
L4:L23L4=IF($C$1="","",NOT(ISERROR(SEARCH("*"&$C$1,$K$4:$K$23))))
K4:K12K4=A4&B4&C4&D4&E4
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:J23Expression=$L4=TRUEtextNO


Assuming you are using your 365 version, I would suggest trying this to see if it does what you want (& no helper columns needed)
Example 1. Search for Index Number 13

25 03 22.xlsm
ABCDE
1Search13
2
3Binder NameIndex NumberPurchzsprOrder NumberUnique
4202057Buyer 90OrdNum 28456Unique 926796
520232Buyer 48OrdNum 77550Unique 451807
6201913Buyer 21OrdNum 60514Unique 291593
720209Buyer 41OrdNum 35296Unique 839225
820207Buyer 74OrdNum 42023Unique 136741
9202526Buyer 77OrdNum 51646Unique 321216
10201912Buyer 83OrdNum 31622Unique 524089
11202361Buyer 74OrdNum 49468Unique 392020
12202017Buyer 48OrdNum 10647Unique 665287
13202213Buyer 38OrdNum 73867Unique 961025
14202480Buyer 34OrdNum 13682Unique 626439
15202589Buyer 16OrdNum 47794Unique 211090
16201948Buyer 11OrdNum 39396Unique 550797
17202529Buyer 73OrdNum 77354Unique 213919
18202078Buyer 20OrdNum 30620Unique 403200
19201939Buyer 38OrdNum 20470Unique 995111
20202170Buyer 80OrdNum 83981Unique 478209
21202051Buyer 87OrdNum 89880Unique 845895
22202443Buyer 2OrdNum 73770Unique 756503
23202593Buyer 70OrdNum 48773Unique 922951
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:K26Expression=AND(SEARCH("|"&$C$1&"|","|"&TEXTJOIN("|",1,$A4:$K4)&"|"),$C$1<>"")textNO



Example 2. Search for Binder Name 2023

25 03 22.xlsm
ABCDE
1Search2023
2
3Binder NameIndex NumberPurchzsprOrder NumberUnique
4202057Buyer 90OrdNum 28456Unique 926796
520232Buyer 48OrdNum 77550Unique 451807
6201913Buyer 21OrdNum 60514Unique 291593
720209Buyer 41OrdNum 35296Unique 839225
820207Buyer 74OrdNum 42023Unique 136741
9202526Buyer 77OrdNum 51646Unique 321216
10201912Buyer 83OrdNum 31622Unique 524089
11202361Buyer 74OrdNum 49468Unique 392020
12202017Buyer 48OrdNum 10647Unique 665287
13202213Buyer 38OrdNum 73867Unique 961025
14202480Buyer 34OrdNum 13682Unique 626439
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:K26Expression=AND(SEARCH("|"&$C$1&"|","|"&TEXTJOIN("|",1,$A4:$K4)&"|"),$C$1<>"")textNO
 
Upvote 0
THank you! The end users don't search by the binder or index numbers. Really appreciate all your options. And your help.

THanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Conditional Formatting VBA
Replies
3
Views
391
Joe4
Joe4
W
  • Solved
Conditional Formatting using a formula based on 2 columns.
Replies
3
Views
125
Peter_SSs
Peter_SSs
A
  • Solved
Need help with worksheet/conditional formatting/macros please
Replies
7
Views
333
Aphten
A
W
  • Solved
Using =IF(AND( or the IFS functions as a formula within Conditional Formatting (CF)
Replies
6
Views
435
Walkon
W
U
  • Solved
CONDITIONAL FORMATTING - Multiple values in cells separated by commas
Replies
3
Views
148
Peter_SSs
Peter_SSs

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