View original (French)

Highlighted cells based on a TextBox

Wyrgle Posted messages 4 Status Membre -  
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   -
Hello everyone,

Here is my problem: I have a list of users in a TextBox. Each user is linked to several references listed on the 'Main' sheet.
Is it possible to highlight (bold font or temporary cell coloring) all the references related to a selected user in the TextBox?

A screenshot to help me explain (I hope!)



Thank you for your help!
Wyrgle

1 réponse

Réponse 1 / 1
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
Hello,

I think that by simply using Conditional Formatting (by selecting: Formula) you should be able to easily achieve what you want....
https://support.microsoft.com/fr-fr/office/appliquer-la-mise-en-forme-conditionnelle-pour-faire-ressortir-des-informations-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?ui=fr-fr&rs=fr-fr&ad=fr
  • You might need to retrieve the selected value from your listbox and place it in a cell (for example in A1) in order to use it in the formula later.


Otherwise, using a macro,
there is also the possibility of using the FINDALL method
https://forums.commentcamarche.net/forum/affich-37621992-methode-find-dans-vba-recherche-de-donnees-sous-excel#findall
  • You will need to slightly modify the code to store the row (which is already the case) AND the column for each result.

Something like 
 'resizing the array ReDim Preserve arMatches(1 To 2,1 to iArr) 'Adding information arMatches(0,iArr) = rFnd.Row ' row arMatches(1,iArr) = rFnd.Column ' column

or directly use the address (instead of separating row and column) 
 ReDim Preserve arMatches(iArr) arMatches(iArr) = rFnd.Address ' will be in the form $A$1 for example


You just need to loop through the array (containing the list of cells corresponding to the searched value) using a FOR loop to apply the desired formatting.

--
Best regards,
Jordane
0