Bedingte Formatierung in VBA Code wandeln

[LauraBeLaura]

Hallo zusammen,

 ich habe auf Sheet 1  $A$3:$E$108 folgende bedingte Formatierung

=MATCH($C3;Sheet2!$A$2:$A$100;0)

Hier wird eine ganze Zeile bis E rot gefärbt wenn in Spalte C ein Wert steht, der in  Sheet 2 Spalte A vorkommt.

Angenommen in Sheet2 A6 steht Mond, und in Sheet 1 C6,C7,C8 steht Mond, dann werden Zeile 6,7 und 8 bis spalte E eingefärbt. Nun möchte ich aber spontan Zeile 8 manuell wieder weiß machen. Das klappt aber nicht weil die Bedingte Formatierung > manuelle Änderung.

Wie kann ich die rote einfärbung einmalig mit VBA machen, damit ich später manuell das rot wieder in Weiß umwandeln kann ohne das eine Bedingte Formatierung das verhindert?

[schauan]

Hallöchen,

kennst Du Dich denn mit VBA aus?
Du könntest in einer Schleife prüfen, ob in der jeweiligen Zelle der Suchbegriff steht.
Im Prinzip hier mal A1 bis A10

For iCnt = 1 to 10
If Cells(icnt, 1).Value = "Mond" Then range(Cells(iCnt, 2),Cells(icnt, 6)).interior.Color=123456
Next

Welche Farbnummer Du brauchst, kannst Du mit der Makroaufzeichnung rausfinden. Das wäre jetzt mal ein direkter Vergleich.

Du kannst aber auch MATCH im Code verwenden.

WorksheetFunction.Match(Sheets("Tabelle1").Range("C3"), Sheets("Tabelle2").Range("A1:A100"), 0)

Allerdings hat die Variante den Nachteil, dass sie bei Nichtvorhandensein des Suchbegriffs einen Fehler bringt. Den müsste man vermeiden. Ich würde stattdessen ggf. WorksheetFunction.CountIf ... > 0 nehmen.

[Fred11]

Hallo LauraBeLaura,

Lösungsansatz ohne VBA:
Die bedingte Formatierung über eine Checkbox steuern (ein- und ausschalten)

Siehe hier:
https://chandoo.org/wp/on-off-conditional-formatting/

Gruß
Fred

[LauraBeLaura]

Hallo,
ja ich kenn mich etwas mit VBA aus. Checkbox ist keine Option, weil ich ja hin und wieder nur 2-3 Zeilen manuell wieder auf weiß umfärben möchte, nicht alle auf einmal.


Mit schleifen habe ich schon rum probiert, bekomme aber die range nicht rein.

" If Cells(icnt, 1).Value = "Mond" Then range(Cells(iCnt, 2),Cells(icnt, 6)).interior.Color=123456 "

In dem Vorschlag wir ja nur nach Mond gesucht, aber ich muss nach allen Suchbegriffen gleichzeitig suchen die in Spalte A stehen.

Also statt   Value = "Mond"   brauch ich sowas wie  Value = Range (Sheet2!A1:A20), aber da hängts dann bei mir.

[schauan]

Hallöchen,

das wäre dann, wie ich schon schrieb, z.B. die Variante mit

If WorksheetFunction.CountIf(Range("A1:A10"), "Mond") > 0 Then ...

range(Cells(iCnt, 2),Cells(icnt, 6)) begrenzt dann flexibel einen Bereich. iCnt entspricht ja dem Schleifenzähler und legt die Zeile fest, die Zahl dahinter die Spalte. Bei mir im Beispiel wäre das das von B bis F.

[LauraBeLaura]

Danke dir André,
aber so ganz peile ich es noch nicht.
" If WorksheetFunction.CountIf(Range("A1:A10"), "Mond") > 0 Then ... " sucht doch wieder nur nach Mond.

Ich versuch es nochmal anders zu erklären, habe mich glaube nicht deutlich genug erklärt.

In Sheet2 Spalte A stehen einige (nicht alle) Monde des Sonnensystems mit (sich ändernde) Namen (Mond, Europa, Deimos ect)


In Sheet1 ist eine Tabelle von allen Himmelskörpern im Sonnensystem, in Spalte C steht der Name.

Ich möchte jetzt in Sheet 1 in der langen Tabelle jede Zeile bis Spalte E rot färben, wenn da in Spalte C einer der Namen steht, die in Sheet 2 in Spalte A stehen.

[schauan]

Hallöchen,

in den Codeschnipseln sind Ranges angegeben, ich dachte, damit wäre das Prinzip ersichtlich. Dann mal etwas ausführlicher
Die Reihenfolge der Parameter von ZÄHLENWENN ist wie bei der Formel im Blatt, also Bereich und Suchbegriff.

Wenn Du einen Begriff in Spalte C suchst, statt

If WorksheetFunction.CountIf(Range("A1:A10"), "Mond") > 0 Then

dann

If WorksheetFunction.CountIf(Range("C1:C10"), "Mond") > 0 Then


Wenn Du auf einenm anderen Blatt suchst, dann muss selbiges noch angegeben werden, z.B.

If WorksheetFunction.CountIf(Sheets("Tabelle1").Range("C1:C10"), "Mond") > 0 Then

Wenn Du den Begriff aus Spalte A suchst, dann

If WorksheetFunction.CountIf(Sheets("Tabelle1").Range("C1:C10"), Range("A1")) > 0 Then


Das wäre wieder eine feste Zelle. In der Schleife nimmst Du statt der 1 bei A1 den Schleifenzähler

If WorksheetFunction.CountIf(Sheets("Tabelle1").Range("C1:C10"), Range("A" & iCnt)) > 0 Then


Starten musst Du das Makro, wenn Tabelle2 aktiv ist. Ist eine andere Tabelle aktiv, müsste man auch diesen Blattnamen hier und beim Einfärben usw. angeben.

[LauraBeLaura]

Alles klar, habs jetzt hin bekommen. Danke schön


Dim i As Long
   For i = 1 To LastRow
     
       If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A16"), Range("C" & i)) > 0 Then
         Rows(i).Interior.ColorIndex = 6
            
      End If
   Next