Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zelle Rot, dann Datensatz kopieren

Zelle Rot, dann Datensatz kopieren
23.10.2014 13:34:46
Alex
Hallo,
ich habe folgendes Problem. Ich will, dass aus dem Arbeitsblatt 1 alle Zellen(sind ausschließlich in A20- A100) die durch bedingte Formatierung rot geworden sind, automatisch in Arbeitsblatt 2 (ab A20) kopiert werden.
Kann mir jemand damit helfen?
Vielen Dank im Voraus
Alex

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zelle Rot, dann Datensatz kopieren
23.10.2014 17:42:23
fcs
Hallo Alex,
die Auswertung der bedingten Formatierung unter VBA ist etwas mühselig.
Ein einfacher Weg wäre hier die Nutzung des Autofilters, um die rotfarbigen Zellen zu kopieren.
Voraussetzung ist, dass die Zelle A19 nicht leer ist.
Ansonsten ist es ggf. einfacher die Bedingung der bedingten Formatierung im Code nachzubilden und dann die entsprechenden Zellen zu kopieren.
Gruß
Franz
'Erstellt unter Excel 2010
Sub Autofilter_Copy()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Set wks1 = ActiveWorkbook.Sheets(1)
Set wks2 = ActiveWorkbook.Sheets(2)
wks2.Range("A20:A100").ClearContents
With wks1
With .Range("A19:A100")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
End With
.AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, 1).Copy
wks2.Range("A20").PasteSpecial Paste:=xlPasteValues
.ShowAllData
.AutoFilterMode = False
End With
End Sub

Anzeige
AW: Zelle Rot, dann Datensatz kopieren
24.10.2014 14:24:52
Ewald
Hallo,
wenn die bed.Formatierung direkt den Zellenhintergrund färbt ist es kein Problem.
mit den Sonderformaten Datenbalken,Farbscalen,Symbole funktioniert es nicht.
Gruß Ewald

OT: Mitteilung für Ewald
25.10.2014 04:34:20
Luc:-?
Hallo, Ewald;
habe mich nun entschlossen, das ThemeColor-Thema vorerst zurückzustellen, da zu komplex und ich erstmal anderes fertig­stellen will.
Zu dem, was auch hier Thema ist:
Habe mir inzwischen deine Lösung angesehen und muss dir insofern recht geben, dass das so natürlich nicht mit einer normalen UDF bewerk­stelligt wdn kann. Das liegt an der hierin integrierten (temporären) Nachbehandlung des BedingtFormats. Eine ZellFml-UDF würde eine (permanente) Vorbehandlung erfordern oder bei permanenter Nachbehandlung zumindest eine physische Entkopplung dieses Teils von der UDF, was aber ziemlich kompliziert ist und wg der Performance und gewisser Unwägbar­keiten allerhöchstens für Einzel­fälle genutzt wdn sollte. Auch für ganze Tab­Blätter ist hingegen deine originale Sub­Prozedur-Methode (mit den von dir gemachten Einschränkungen, v.a. bzgl MenüPkt1) sicher gut geeignet.
Evtl baue ich deine GrundIdee in eine Zell­Auswahl-UDF ein (auch, weil meine noch nicht fertig und außerdem soviel aufwendiger ist, dass ich sie lieber separat anwende als hierfür mit Einschränkungen zu arbeiten), was dann aber eine permanente Vorbehandlung des Bedingt­Formats erfordern würde. Das habe ich aber noch nicht end­gültig entschieden.
Gruß + schöWE, Luc :-?

Anzeige
AW: OT: Mitteilung für Ewald
26.10.2014 00:30:15
Ewald
Hallo Luc,
ThemeColor bzw. TAS war ja auch eher theoretischer Natur, das eventuelle Rückrechnen eines Farbwerts auf seinen Grundwert kann ja nur erfolgen wenn die Farbe mit TAS gesetzt wird. Dies ist aber nur mit vba möglich.Da aber Excel die TAS-Eingabe an wahrscheinlich vorhandene interne Werte anpaßt, ist eine Rückrechnung garnicht vorgesehen und damit auch nicht zu erreichen.
zum Thema hier.
habe mal eine UDF geschrieben und konnte auch die Farbe von Datenbalken und Farbscala auslesen. Habe das aber wieder rausgenommen. Es macht einfach keinen Sinn diese bei Farbauslesung zu berücksichtigen.
Datenbalken und Farbscala sind eigentlich ja Bereichsformatierungen und keine Zellenformatierung. Die Symbolsätze stehen eh außenvor.
Die UDF gibt die Farbe der bed.Formatierung zurück,falls eine aktiv ist. Ist keine aktiv wird 0 zurückgegeben. hat die Zelle keine bed. Formatierung wird -1 zurückgegeben.
Im aufrufenden Code kann dann darauf reagiert werden, ob dann zB. die Zellfarbe wiedergegeben wird.
die UDF
Public Function Bedforcol(Bezug As Range) As Long
Dim i As Long
Dim strFor1 As String   'Zellformat
Dim strFor2 As String   'Zusatzformat
Dim strFor3 As String   'Testformat
Dim strFor4 As String    'Zellformat
Dim t As Double         'Type
Dim x As Double
Dim y As Double
Dim z As Long
Dim w As Byte
Dim Farbe As Long
Dim Ergebnis As String
w = Bezug.FormatConditions.Count
If w  0 Then Farbe = Bezug.FormatConditions(y).Interior.Color
Bedforcol = Farbe
End Function
der Aufruf könnte dann so aussehen
Sub Farbsumme()
Dim zelle As Range
Dim y As Long
For Each zelle In Selection
If Bedforcol(zelle) = 255 Then
y = y + zelle.Value
End If
Next
MsgBox y
End Sub
Hier wird jetzt die Summe der Zellen wiedergegeben, die durch Bedfor rot gefärbt sind.
Es lassen sich aber auch andere Aufgaben damit ausführen, wie hier zB. das kopieren.
Für andere Infos der Bedfor muß dann eine andere UDF geschrieben werden (zB. aktive Bedfor), die aber sehr schnell anhand dieser UDF angepaßt werden kann.
Gruß Ewald

Anzeige
AW-OT: Danke für dein Material, ...
26.10.2014 02:02:53
Luc:-?
…Ewald,
werde ich mir ansehen. Was MenüPkt1 des Bedingt­Formats betrifft, liegst du natürlich richtig, denn eigentlich ist das ja auch gar keine „klassische“ Bedingt­Forma­tierung. Spätestens ab Xl14 kann auch die Farbe der Balken vorgegeben wdn, ist also auch auslesbar. Das Problem sind dann die Balken selber, für die man eine Eratzlösung finden müsste, was etwas einfacher als bei Icons wäre. Aber, wenn man das für die Erzeugung einer HTML-Tabelle nutzen will, müsste man in diesen beiden Fällen eigentlich Hinter­grund­Bilder verwenden, wenn es original aussehen soll, was aus Gründen der Univer­salität nicht akzeptabel ist. Ein Ersatz der Symbole im Icons-Fall würde auf jeden Fall stark abweichen, da die Original­Symbole nicht verfügbar sind.
Übrigens, genau bis zu dieser Stelle, also noch inkl Farb­Skalen, bin ich mit meiner Methode auch gekommen. Es fehlt aller­dings noch die Behandlung mehrfacher Regel­Erfüllung und eine noch­malige Über­prüfung des bisher Erreichten, was ich bislang zurück­gestellt habe.
Was ThemeColor betrifft, hast du natürlich recht. Stellt man exakt die gleiche Farbe, zB mit einem FärbePgm, ein, gibt's auch kein T&S. Damit wäre auch alles bzgl Rück­rechnung rein theoretisch.
Gruß, Luc :-?

Anzeige
AW: AW-OT: Danke für dein Material, ...
26.10.2014 17:25:48
Blaumann
Hallo,
ist es denn auch möglich alle Zellen abzufragen egal ob sie manuell oder per bed.Formatierung gefärbt wurden.
Es wurde auch erwähnt das es auch bei Datenbalken geht, ist es schwer dies umzusetzen.
Gruß Peter

Ja, wenn du es selber versuchen willst, ...
26.10.2014 18:34:26
Luc:-?
…Peter,
kann es ziemlich aufwendig wdn. Bei Verwendung von Ewalds Methode (aus MGruppe3, nur ab Xl12/14!) ist es deutlich einfacher, weil ja jetzt die entsprd PgmCodes auch vorliegen, sie müssen nach Ewalds Aussage nur entsprd angepasst wdn, weil seine Pgmm nicht universell für alle Bedingt­Formate ausgelegt sind.
Bzgl MGruppe2 (auch meine Methode) stehen zZ nur PgmCodes anderer Anbieter für Teil­Lösungen zV. Dabei muss im Regel=Fml-Fall viel mehr Anpassungs­Aufwand getrieben wdn, falls so etwas überhaupt angeboten wird…
Also musst du dich zuvor unbedingt intensiv VBA-seitig mit der Bedingt­Formatierung befassen (es sei denn, jemand macht das für dich).
Gruß, Luc :-?

Anzeige
AW: Ja, wenn du es selber versuchen willst, ...
26.10.2014 19:30:20
Ewald
Hallo,
zunächst noch ein Hinweis, hatte ich übersehen. die Funktion nicht als Public deklarieren,
@Peter
Du kannst auch beide abfragen, dies geschieht aber nicht in der Funktion sondern im Aurufmakro
als Beispiel.
Sub Farbsumme()
Dim zelle As Range
Dim y As Long
For Each zelle In Selection
If Bedforcol(zelle) = 255 Or zelle.Interior.Color = 255 Then
y = y + zelle.Value
End If
Next
MsgBox y
End Sub
Die Anpassung für Datenbelken ist auch leicht möglich, weil die Variablen dafür noch enthalten sind.
Wenn Datenbalken vorhanden sind wird die Variable z gesetzt und die Variable x enthält den Typ (bei Datenbalken die 3.
Wenn z > 0 und x = 3 ist kann man dann die Farbe abfragen.
Da der Datenbalken aber zwei Farben hat (im Normalfall rot für negtive Zahlen und die Auswahlfarbe für positve Zahlen.) Man muß also das Value der Zelle auslesen um die richtige Farbe auszulesen.
Dies sollte aber kein größeres Problem sein.
Erschwerend kommt aber hinzu das auch noch eine andere Bedfor aktiv sein kann.
Hier muß man sich entscheiden was man haben will.
setzt man zuerst die Farbe auf y Variable (normale Zellenformatierung)
und dann die Farbe auf die z Variable (Datenbalken)
wird die Farbe des Datenbalkens wiedergegeben
im umgekehrten Fall dann die Farbe der normalen Zellenformatierung.
Man könnte dies netürlich auch mit einem weiteren Parameter steuern oder auch beide Farben ausgeben, doch habe ich dies erstmal rausgelassen.
Gruß Ewald

Anzeige
AW: Ja, wenn du es selber versuchen willst, ...
26.10.2014 23:02:23
Blaumann
Hallo Ewald,
verstehe ich nicht, der Datenbalken hat doch nur eine Farbe
Gruss Peter

AW: Ja, wenn du es selber versuchen willst, ...
27.10.2014 00:05:43
Ewald
Hallo Peter,
dann schau mal
Userbild
beide Zellen haben einen Datenbalken und eine Hintergrundfarbe
manuelle Eingabe -30 bis 50
in der oberen Zelle ein Minuswert
in der unteren Zelle der Pluswert
Gruß Ewald

Nur 1 Farbe ist Xl12, du hast Xl14 angegeben! owT
27.10.2014 04:08:07
Luc:-?
:-?

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige