Ciao Fabio,
Forse mi sono espresso male, scusatemi. Per database io intendo dati inseriti nelle singole celle comprensive di cognome e nome.
La ricerca del dato vorrei farla per cognome e nome. Se trova il dato, mi piacerebbe che la riga si colorasse di giallo, se invece il cognome e nome compare per più volte, le righe vorrei che si colorassero di un colore diverso.
In sintesi: Inserisco cognome e nome, invio la ricerca, il dato con data recente : giallo mentre lo stesso dato con data meno recente, colore diverso.
Una volta che clicco sulla cella colorata, lo sfondo vorrei che tornasse bianco.
Bene!
Per:
- Trovare, su ogni foglio del file interessato, ogni istanza di un testo fornito dall'utente e colorare lo sfondo della prima istanza in giallo ed eventuali altri istanze in verde, e
- togliere il colore dello sfondo di ogni istanza nel file del testo della cella su cui l'utente faccia
doppio clic, prova qualcosa del genere:
- Alt+F11 per aprire l'editor di VBA
- Alt+IM per inserire un nuovo modulo di codice
- Nel nuovo modulo vuoto, incolla il seguente codice:
'=========>>
Option Explicit
Public FindWhat As Variant
Public sMsg As String
Public sTitle As String
Public iButtons As Long
Public Const iPrimoColore As Long = vbYellow '<<=== Modifica
Public Const iSecondoColore As Long = vbGreen '<<=== Modifica
'--------->>
Public Sub Tester()
Call CercaTesto
End Sub
'--------->>
Public Sub CercaTesto(Optional bCancella As Boolean)
Dim WB As Workbook
Dim SH As Worksheet
Dim SearchRange As Range
Dim FoundCells As Range
Dim FoundCell As Range
Dim bTrovato As Boolean
Dim TextToFind As Variant
Dim rngRicerca As Range
Dim i As Long
If Not bCancella Then
FindWhat = Application.InputBox( _
Prompt:="Inserisci Cognome e Nome!!", _
Title:="COGNOME E NOME", _
Type:=2)
If FindWhat = False Then
sMsg = "Hai cancellato!"
sTitle = "CODICE TERMINATO!"
iButtons = vbCritical
GoTo XIT
ElseIf FindWhat = vbNullString Then
sMsg = "Non hai immesso un nome!"
sTitle = "CODICE TERMINATO!"
iButtons = vbCritical
GoTo XIT
End If
End If
Set WB = ThisWorkbook
For Each SH In WB.Sheets
With SH
Set SearchRange = .UsedRange
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If Not FoundCells Is Nothing Then
Intersect(FoundCells.EntireRow, .UsedRange). _
Interior.Color = IIf(bCancella, xlNone, iSecondoColore)
If Not bTrovato Then
Intersect(FoundCells(1).EntireRow, .UsedRange). _
Interior.Color = IIf(bCancella, xlNone, iPrimoColore)
bTrovato = True
End If
End If
End With
Next SH
If bTrovato Then
sMsg = "Finito!"
If bCancella Then sMsg = sMsg _
& vbNewLine _
& "Lo sfondo di ogni cella che contiene il testo " _
& FindWhat & "è stato cancellato"
sTitle = "REPORT"
iButtons = vbInformation
Else
sMsg = "Il nome " & FindWhat & " non è stato trovato!"
sTitle = "REPORT"
iButtons = vbCritical
End If
XIT:
FindWhat = vbNullString
Call MsgBox( _
Prompt:=sMsg, _
Buttons:=iButtons, _
Title:=sTitle)
End Sub
'--------->>
Public Function FindAll(SearchRange As Range, _
FindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlWhole, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional MatchCase As Boolean = False, _
Optional BeginsWith As String = vbNullString, _
Optional EndsWith As String = vbNullString, _
Optional BeginEndCompare As VbCompareMethod = _
vbTextCompare) As Range
'\ Chip Pearson http://www.cpearson.com/excel/findall.aspx
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This searches the range specified by SearchRange and returns a Range object
' that contains all the cells in which FindWhat was found. The search parameters to
' this function have the same meaning and effect as they do with the
' Range.Find method. If the value was not found, the function return Nothing. If
' BeginsWith is not an empty string, only those cells that begin with BeginWith
' are included in the result. If EndsWith is not an empty string, only those cells
' that end with EndsWith are included in the result. Note that if a cell contains
' a single word that matches either BeginsWith or EndsWith, it is included in the
' result. If BeginsWith or EndsWith is not an empty string, the LookAt parameter
' is automatically changed to xlPart. The tests for BeginsWith and EndsWith may be
' case-sensitive by setting BeginEndCompare to vbBinaryCompare. For case-insensitive
' comparisons, set BeginEndCompare to vbTextCompare. If this parameter is omitted,
' it defaults to vbTextCompare. The comparisons for BeginsWith and EndsWith are
' in an OR relationship. That is, if both BeginsWith and EndsWith are provided,
' a match if found if the text begins with BeginsWith OR the text ends with EndsWith.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
Dim XLookAt As XlLookAt
Dim Include As Boolean
Dim CompMode As VbCompareMethod
Dim Area As Range
Dim MaxRow As Long
Dim MaxCol As Long
Dim BeginB As Boolean
Dim EndB As Boolean
CompMode = BeginEndCompare
If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
XLookAt = xlPart
Else
XLookAt = LookAt
End If
' this loop in Areas is to find the last cell
' of all the areas. That is, the cell whose row
' and column are greater than or equal to any cell
' in any Area.
For Each Area In SearchRange.Areas
With Area
If .Cells(.Cells.Count).Row > MaxRow Then
MaxRow = .Cells(.Cells.Count).Row
End If
If .Cells(.Cells.Count).Column > MaxCol Then
MaxCol = .Cells(.Cells.Count).Column
End If
End With
Next Area
Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)
On Error GoTo 0
Set FoundCell = SearchRange.Find(what:=FindWhat, _
after:=LastCell, _
LookIn:=LookIn, _
LookAt:=XLookAt, _
SearchOrder:=SearchOrder, _
MatchCase:=MatchCase)
If Not FoundCell Is Nothing Then
Set FirstFound = FoundCell
Do Until False ' Loop forever. We'll "Exit Do" when necessary.
Include = False
If BeginsWith = vbNullString And EndsWith = vbNullString Then
Include = True
Else
If BeginsWith <> vbNullString Then
If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, _
BeginEndCompare) = 0 Then
Include = True
End If
End If
If EndsWith <> vbNullString Then
If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, _
BeginEndCompare) = 0 Then
Include = True
End If
End If
End If
If Include = True Then
If ResultRange Is Nothing Then
Set ResultRange = FoundCell
Else
Set ResultRange = Application.Union(ResultRange, FoundCell)
End If
End If
Set FoundCell = SearchRange.FindNext(after:=FoundCell)
If (FoundCell Is Nothing) Then
Exit Do
End If
If (FoundCell.Address = FirstFound.Address) Then
Exit Do
End If
Loop
End If
Set FindAll = ResultRange
End Function
'<<=========
- Ctrl+R per accedere alla finestra Project Explorer ('Gestione progetti')
- Fai doppio clic sul modulo ThisWorkbook (Questa_cartella_di_Lavoro) del file e incolla il seguente codice
'=========>>
Option Explicit
'--------->>
Private Sub Workbook_SheetBeforeDoubleClick(ByVal SH As Object, _
ByVal Target As Range, Cancel As Boolean)
If Target.Interior.Color = iPrimoColore _
Or Target.Interior.Color = iSecondoColore Then
FindWhat = Target.Value
If FindWhat <> vbNullString Then
Call CercaTesto(True)
End If
End If
End Sub
'<<=========
- Alt+Q per chiudere l'editor di VBA e tornare a Excel
- Salva il file con l’estensione xlsm
- Alt+F8 per aprire la finestra di gestione delle macro
- Seleziona Tester
- Esegui
Potresti scaricare il mio file di prova Fabio0180305.xlsm
===
Regards,
Norman
