Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zelle Rot, dann Datensatz kopieren

Betrifft: Zelle Rot, dann Datensatz kopieren von: Alex
Geschrieben am: 23.10.2014 13:34:46

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

  

Betrifft: AW: Zelle Rot, dann Datensatz kopieren von: fcs
Geschrieben am: 23.10.2014 17:42:23

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



  

Betrifft: AW: Zelle Rot, dann Datensatz kopieren von: Ewald
Geschrieben am: 24.10.2014 14:24:52

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


  

Betrifft: OT: Mitteilung für Ewald von: Luc:-?
Geschrieben am: 25.10.2014 04:34:20

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


  

Betrifft: AW: OT: Mitteilung für Ewald von: Ewald
Geschrieben am: 26.10.2014 00:30:15

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 < 1 Then
    Bedforcol = -1
    Exit Function
End If
 strFor4 = Bezug.NumberFormat
For i = 1 To Bezug.FormatConditions.Count
     t = Bezug.FormatConditions(i).Type
        If t = 3 Or t = 4 Or t = 6 Then
            z = i
            x = t
            GoTo Weiter
            
        Else
           
            strFor1 = Bezug.FormatConditions(i).NumberFormat
            strFor2 = Chr(34) & "(" & i & ")" & Chr(34)
            If IsNumeric(Bezug.Value) Or IsDate(Bezug.Value) Then
                strFor3 = strFor2 & " " & strFor1
            Else
                strFor3 = "@" & " " & strFor2
            End If
                Bezug.NumberFormat = "General"
                With Bezug.FormatConditions(i)
                    .NumberFormat = strFor3
                End With
        End If
Weiter:
Next
If IsNumeric(Bezug.Value) Or IsDate(Bezug.Value) Then
    If Mid(Bezug.Text, 1, 1) = "(" Then
        y = Mid(Bezug.Text, 2, 1)
    Else
        y = 0
    End If
Else
    If Right(Bezug.Text, 1) = ")" Then
        y = Mid(Bezug.Text, Len(Bezug.Text) - 1, 1)
    Else
        y = 0
    End If
End If
For i = 1 To Bezug.FormatConditions.Count
    t = Bezug.FormatConditions(i).Type
    If t = 3 Or t = 4 Or t = 6 Then
        GoTo Ende
    Else
     Bezug.NumberFormat = strFor4
     With Bezug.FormatConditions(i)
        .NumberFormat = strFor1
     End With
    End If
Ende:
Next
For i = 1 To Bezug.FormatConditions.Count
    t = Bezug.FormatConditions(i).Type
    If t = 10 Then
        If Bezug.Value = "" Then y = i
    End If
    If t = 16 Then
        If IsError(Bezug.Value) Then y = 1
    End If
Next
    
    If y > 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


  

Betrifft: AW-OT: Danke für dein Material, ... von: Luc:-?
Geschrieben am: 26.10.2014 02:02:53

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


  

Betrifft: AW: AW-OT: Danke für dein Material, ... von: Blaumann
Geschrieben am: 26.10.2014 17:25:48

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


  

Betrifft: Ja, wenn du es selber versuchen willst, ... von: Luc:-?
Geschrieben am: 26.10.2014 18:34:26

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


  

Betrifft: AW: Ja, wenn du es selber versuchen willst, ... von: Ewald
Geschrieben am: 26.10.2014 19:30:20

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


  

Betrifft: AW: Ja, wenn du es selber versuchen willst, ... von: Blaumann
Geschrieben am: 26.10.2014 23:02:23

Hallo Ewald,

verstehe ich nicht, der Datenbalken hat doch nur eine Farbe

Gruss Peter


  

Betrifft: AW: Ja, wenn du es selber versuchen willst, ... von: Ewald
Geschrieben am: 27.10.2014 00:05:43

Hallo Peter,

dann schau mal



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


  

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

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Zelle Rot, dann Datensatz kopieren"