Zelle Rot, dann Datensatz kopieren

Forumthread: 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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

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 fertigstellen 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 bewerkstelligt 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ägbarkeiten allerhöchstens für Einzelfälle genutzt wdn sollte. Auch für ganze TabBlätter ist hingegen deine originale SubProzedur-Methode (mit den von dir gemachten Einschränkungen, v.a. bzgl MenüPkt1) sicher gut geeignet.
Evtl baue ich deine GrundIdee in eine ZellAuswahl-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 BedingtFormats erfordern würde. Das habe ich aber noch nicht endgültig entschieden.
Gruß + schöWE, Luc :-?

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

AW-OT: Danke für dein Material, ...

26.10.2014 02:02:53

Luc:-?

…Ewald,
werde ich mir ansehen. Was MenüPkt1 des BedingtFormats betrifft, liegst du natürlich richtig, denn eigentlich ist das ja auch gar keine „klassische“ BedingtFormatierung. 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 HintergrundBilder verwenden, wenn es original aussehen soll, was aus Gründen der Universalität nicht akzeptabel ist. Ein Ersatz der Symbole im Icons-Fall würde auf jeden Fall stark abweichen, da die OriginalSymbole nicht verfügbar sind.
Übrigens, genau bis zu dieser Stelle, also noch inkl FarbSkalen, bin ich mit meiner Methode auch gekommen. Es fehlt allerdings noch die Behandlung mehrfacher RegelErfüllung und eine nochmalige Überprüfung des bisher Erreichten, was ich bislang zurückgestellt 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ückrechnung rein theoretisch.
Gruß, Luc :-?

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 BedingtFormate ausgelegt sind.
Bzgl MGruppe2 (auch meine Methode) stehen zZ nur PgmCodes anderer Anbieter für TeilLösungen zV. Dabei muss im Regel=Fml-Fall viel mehr AnpassungsAufwand getrieben wdn, falls so etwas überhaupt angeboten wird…
Also musst du dich zuvor unbedingt intensiv VBA-seitig mit der BedingtFormatierung befassen (es sei denn, jemand macht das für dich).
Gruß, Luc :-?

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

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:-?

:-?

Beliebteste Forumthreads (12 Monate)

Zelle Rot, dann Datensatz kopieren

Forumthread: Zelle Rot, dann Datensatz kopieren

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Zelle Rot, dann Datensatz kopieren

Forumthread: Zelle Rot, dann Datensatz kopieren

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread