Microsoft Excel

Herbers Excel/VBA-Archiv

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

Formel ColorIndex | Herbers Excel-Forum


Betrifft: Formel ColorIndex von: Josef
Geschrieben am: 02.03.2010 10:49:07

Hallo!

Wenn ich in der Spalte G in der Tabelle1 einen ColorIndex 15 habe (Grau) möchte ich in der spalte B in der gleichen Zeile ein ü setzen.

=WENN(G2;ColorIndex=15;"ü") ????

Wie würde hier bitte eine funktionierende Formellösung lauten?

Danke

Josef

  

Betrifft: AW: Formel ColorIndex von: hary
Geschrieben am: 02.03.2010 10:53:25

Hallo
wie kommt die Farbe in SpalteG.
Geht ME nur mit VBA oder einer UDF.
gruss hary


  

Betrifft: AW: Formel ColorIndex von: Josef
Geschrieben am: 02.03.2010 10:57:53

Hallo!

Danke für Deine Antwort. Die Farbe wurde mittels einer bedingten Formatierung in die Spalte G eingetragen.

Josef


  

Betrifft: Dann benutze die Regel dieser von: Renee
Geschrieben am: 02.03.2010 10:59:55

bedingten Formatierung um eine Formel zu konstruieren, Josef

GreetZ Renée


  

Betrifft: AW: Dann benutze die Regel dieser von: Josef
Geschrieben am: 02.03.2010 11:33:20

Hallo Renee!

Wenn ich nur wüßte wo ich damit anfangen soll. Ein paar Tests habe ich gemacht. Aber allesamt waren für die Katz.

Josef


  

Betrifft: Dann zeig uns doch die Bedingung! (owT) von: Renee
Geschrieben am: 02.03.2010 11:34:50




  

Betrifft: AW: Dann benutze die Regel dieser von: hary
Geschrieben am: 02.03.2010 11:38:01

Hallo
so in etwa.
=wenn("hier die Formel der Regel";"ü";"")
gruss hary


  

Betrifft: AW: Dann benutze die Regel dieser von: Josef
Geschrieben am: 02.03.2010 11:47:52

Hallo!

Hier wäre die Formel der bedingten Formatierung, wobei der Wert "hygiene" über eine Variable mittels VBA eingetragen wird.

siehe https://www.herber.de/forum/messages/1142579.html

=ODER(ZÄHLENWENN(G4232;"*hygiene*"))

Deswegen wollte ich das "ü" über den ColorIndex ermitteln.


Josef


  

Betrifft: Was soll ein ODER mit nur 1 Bedingung? (owt) von: Renee
Geschrieben am: 02.03.2010 11:48:59




  

Betrifft: AW: Was soll ein ODER mit nur 1 Bedingung? (owt) von: Josef
Geschrieben am: 02.03.2010 11:55:15

Normalerweise war die formel für drei abfragen gedacht. Deswegen ODER.
Nachdem mir bei einer Abfrage kein Fehler kam, ließ ich das ODER drinnen. Nehme ich es halt jetzt raus.

Danke

josef


  

Betrifft: Sorry ich bin raus "Würmer aus der Nase" (owT) von: Renee
Geschrieben am: 02.03.2010 12:04:09




  

Betrifft: AW: Was soll ein ODER mit nur 1 Bedingung? (owt) von: hary
Geschrieben am: 02.03.2010 12:05:55

Hallo
so?

Tabellenblattname: Tabelle1

 

B

C

D

E

F

G

4232

ü

 

 

 

 

aaahygiene


Benutzte Formeln:
B4232: =WENN(ZÄHLENWENN(G4232;"*hygiene*");"ü";"")
gruss hary


  

Betrifft: AW: Was soll ein ODER mit nur 1 Bedingung? (owt) von: Josef
Geschrieben am: 02.03.2010 12:17:09

Hallo hary!

Danke nochmals für Deine Antwort. dies würde im Prinzip alles passen. Mein Problem ist jetzt jedoch noch, dass sich der Text jederzeit ändern kann. Also statt "hygiene" kann jetzt der Wert "kontrast" oder ein anderes Mal der Wert " Institut" usw. eingetragen sein.
Die Werte werden in einer Zelle in einem Tabellenblatt eingetragen über eine Variable mittels VBA wird dann die Bedingte Formel nach Markierung der Spalte G in diese eingetragen.

Aber Deine Lösung ist im Prinzip das was ich suchte.


Josef


  

Betrifft: AW: Was soll ein ODER mit nur 1 Bedingung? (owt) von: Josef
Geschrieben am: 02.03.2010 12:24:39

Hallo hary!

So würde es glaube ich klappen:

=WENN(ZÄHLENWENN(G4;[Masterprog.xla]Tabelle1!$BE$1);"ü";"")

Einfach die Zelle mit dem Eintrag abfragen.

Danke, Du hast mir sehr geholfen.

Schöenen Tag noch.

Josef


  

Betrifft: AW: evtl auch... von: hary
Geschrieben am: 02.03.2010 12:26:54

Hallo
...ungetestet in den Code.

bereich.FormatConditions(1).Interior.ColorIndex = 15
bereich.offset(0,-7) = "ü"   'neu

gruss hary


  

Betrifft: AW: evtl auch... von: Josef
Geschrieben am: 02.03.2010 12:46:52

Hallo hary!

Habe es gerade getestet.

Bekomme hier einen Fehler:

bereich.offset(0,-7) = Anwendungs-oder objektdefinierter Fehler

Josef


  

Betrifft: AW: evtl auch... von: Josef
Geschrieben am: 02.03.2010 13:16:22

Hallo hary!

Mit

bereich.Offset(1, -5) = "ü" 'neu

wird das ü in der Spalte B jetzt eingetragen.

Aber leider in jeder Zeile. Ich nehme an, weil der Bereich abgefragt wird. Und der Bereich ist die ganze Spalte G.

Josef


  

Betrifft: AW: teste mal... von: hary
Geschrieben am: 02.03.2010 15:47:31

Hallo Josef
versuchs mal so mit Schleife und einer neuen Variablen c. Ungeprueft da ich sonst die Mappen nachbauen muesste.

Dim c As Range 'neue Variable
'------------ bis hier alter Code-----------
With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(I2;""*" & wx & "*"");ZÄHLENWENN(I2;""*" & xy & "*"");ZÄHLENWENN(I2;""* _
 _
" & yz & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15
    '----- Schleife für bereich einfügen, prueft ob im bereich die Zelle Zahl >0 hat  ---------- _
-----
    For Each c In bereich
     If c > 0 Then c.Offset(0, -5) = "ü"
     Next
    '------------ ende Schleife ------------
End With
  ' ---------------weiter alter Code-------------

gruss hary


  

Betrifft: AW: teste mal... von: Josef
Geschrieben am: 02.03.2010 19:25:45

Hallo hary!

Danke für diesen Lösungsvorschlag. Werde ihn gleich morgen früh in der FA. testen.

Melde mich morgen wieder.

Schönen Abend noch.


Josef


  

Betrifft: AW: teste mal... von: Josef
Geschrieben am: 04.03.2010 06:34:47

Hallo hary!

Guten Morgen!
Konnte erst heute den Code testen. War erst heute in der Firma.

Es wird auch mit diesem Code in jeder Zeile im Bereich, also überall ein ü eingetragen.

Wahrscheinlich deshalb, weil es in der Spalte G in jeder Zeile einen Wert gibt.

'----- Schleife für bereich einfügen, prueft ob im bereich die Zelle Zahl >0 hat


Der gesuchte Wert soll ja in der Spalte G gefunden und markiert werden. und bei jedem gefundenen Wert sollte in der jeweiligen Zeile in der Spalte B ein ü eingetragen werden.

Josef


  

Betrifft: AW: teste mal... von: Josef
Geschrieben am: 04.03.2010 06:51:01

Hallo hary!

Habe jetzt eine Beispieldatei angefertigt.
Ich hoffe, ich hoffe ich habe keinen Fehler gemacht.

https://www.herber.de/bbs/user/68365.xls


Josef




  

Betrifft: AW:mal mit instr von: hary
Geschrieben am: 04.03.2010 09:02:25

Hallo
Hab mal mit Instr() gebastelt.
Hier als separates Makro fuer Deine Beispielmappe:

 Sub b()
Dim c As Range
Dim bereich As Range
Dim wks As Worksheet
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
 For Each c In bereich
     If InStr(c, "Grund") <> 0 Then c.Offset(0, -5) = "ü"
     Next
End Sub

und hier mal ungetestet wegen fehlender 2.Mappe
'----- Schleife für bereich einfügen, prueft ob im bereich die Zelle inhalt wx hat  ----------  _
_
-----
    For Each c In bereich
     If InStr(c, wx) <> 0 Then c.Offset(0, -5) = "ü"
     Next
    '------------ ende Schleife ------------

gruss hary


  

Betrifft: AW: AW:mal mit instr von: Josef
Geschrieben am: 04.03.2010 09:10:13

Hallo hary!

Guten Morgen. Danke für Deine Rückantwort.

Bin jetzt den Test mit folgendem Makro gefahren:

Sub BedingtesFormat_KapitelG_1()
Dim wx
Dim wx1
Dim c As Range 'neue Variable
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim bereich As Range

Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
Set wks1 = Workbooks("Masterfile.xls").Worksheets("Tabelle1")
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
wx = wks.Range("BE1")
wx1 = wks.Range("BE2")

With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(G2;""*" & wx & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15

    For Each c In bereich
     If InStr(c, wx) <> 0 Then c.Offset(0, -5) = "ü"
     Next

End With
End Sub



Eingetragen wurde jetzt schon mal in der Zelle B2 ein ü. In den restlichen Zellen in der Spalte B blieb bei einem gefundenen Wert "grund" alles leer.

Josef


  

Betrifft: AW: AW:leider ein Irrtum von: Josef
Geschrieben am: 04.03.2010 09:12:35

Hallo hary!

War kein Eintrag, war der alte Eintrag über eine Formel.

Josef


  

Betrifft: AW: AW:leider ein Irrtum von: Josef
Geschrieben am: 04.03.2010 09:32:51

Hallo hary!

Auch mit folgendem Makro kein Eintrag in Spalte B.
Neu ist wx2 = "BE3"
"BE3" wäre "Grund" mit den Anführungszeichen

Sub BedingtesFormat_KapitelG_1()
Dim wx
Dim wx1
Dim wx2
Dim c As Range 'neue Variable
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim bereich As Range

Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
Set wks1 = Workbooks("Masterfile.xls").Worksheets("Tabelle1")
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
wx = wks.Range("BE1")
wx1 = wks.Range("BE2")
wx2 = wks.Range("BE3")

With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(G2;""*" & wx & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15

    For Each c In bereich
     If InStr(c, wx2) <> 0 Then c.Offset(0, -5) = "ü"
     Next

End With
End Sub


Josef


  

Betrifft: AW: mmh von: hary
Geschrieben am: 04.03.2010 09:51:25

Moin
hier mal mit 2 Makros, ausfuehren durch Button. Evtl. liegt's an der gross und kleinschreibung.
https://www.herber.de/bbs/user/68370.xls
gruss hary
Ansonsten werde ich heute Nachmittag mal beide Mappen nachbauen und es dann nochmal versuchen.
Ich kann ja nur vom Beispiel ausgehen.


  

Betrifft: AW: mmh von: Josef
Geschrieben am: 04.03.2010 09:55:35

Hallo hary!

Habe es jetzt mit Deinem 1. Makro Beispiel versucht:
Siehe Makro "

Sub Kapitel_G_SpalteB_1() weiter unten.
Sub BedingtesFormat_KapitelG_1()
Dim wx
Dim wx1

Dim c As Range 'neue Variable
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim bereich As Range

Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
Set wks1 = Workbooks("Masterfile.xls").Worksheets("Tabelle1")
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
wx = wks.Range("BE1")
wx1 = wks.Range("BE2")

With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(G2;""*" & wx & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15
End With
Call Kapitel_G_SpalteB_1
End Sub

Sub Kapitel_G_SpalteB_1()
   Dim c As Range
   Dim bereich As Range
   Dim wks As Worksheet
   Dim wx2
   Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
   wx2 = wks.Range("BE3")
   Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
    For Each c In bereich
        If InStr(c, wx2) <> 0 Then c.Offset(0, -5) = "ü"
        Next
   End Sub
Mit Deinem Makro mit der Zeile If InStr(c, "Grund") <> 0 Then c.Offset(0, -5) = "ü"
würde es funktionieren.
Mit meinem Umbau nicht:

If InStr(c, wx2) <> 0 Then c.Offset(0, -5) = "ü"

Warum eigentlich nicht. Jetzt sollte ja eigentlich alles nur mehr von einem kleinen Fehler abhängen. Habe ich die Variable falsch angelegt?



Josef


  

Betrifft: AW: mmh von: Josef
Geschrieben am: 04.03.2010 10:08:32

Hallo hary!

Ich glaube, dank Deiner letzten Meldung weiss ich jetzt woran es liegt.

Gebe ich in der UserForm "Grund" (ohne Anführungszeichen) ein dann passt alles. Gebe ich den "grund" klein geschrieben ein, dann gibt es keinen Eintrag. Es wäre aber mit *Wildcard* für mich wichtig, da es ja diese Werte manchmal auch zwischen den Wörten zu finden gibt.


Sub BedingtesFormat_KapitelG_1()
Dim wx
Dim wx1

Dim c As Range 'neue Variable
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim bereich As Range

Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
Set wks1 = Workbooks("Masterfile.xls").Worksheets("Tabelle1")
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
wx = wks.Range("BE1")
wx1 = wks.Range("BE2")

With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(G2;""*" & wx & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15
End With
Call Kapitel_G_SpalteB_1
End Sub

Sub Kapitel_G_SpalteB_1()
   Dim c As Range
   Dim bereich As Range
   Dim wks As Worksheet
   Dim wx
   Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
   wx = wks.Range("BE1")
   Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
    For Each c In bereich
        If InStr(c, wx) <> 0 Then c.Offset(0, -5) = "ü"
        Next
   End Sub

Josef


  

Betrifft: AW: mmh von: hary
Geschrieben am: 04.03.2010 10:21:24

Hallo
Mit Ucase wandelt man den Text in Grossbuchstaben um. Also Zellinhalt und Suchtext wird in Grossbuchstaben(nur im Code) umgewandelt.
https://www.herber.de/bbs/user/68373.xls
gruss hary


  

Betrifft: AW: mmh von: Josef
Geschrieben am: 04.03.2010 10:37:49

Hallo hary!

So gelingt es jetzt mit einem Suchbegriff in der TextBox2:

Sub BedingtesFormat_KapitelG_1()
Dim wx
Dim wx1

Dim c As Range 'neue Variable
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim bereich As Range

Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
Set wks1 = Workbooks("Masterfile.xls").Worksheets("Tabelle1")
Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
wx = wks.Range("BE1")
wx1 = wks.Range("BE2")

With wks1
bereich.FormatConditions.Delete
    bereich.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ODER(ZÄHLENWENN(G2;""*" & wx & "*""))"
    bereich.FormatConditions(1).Interior.ColorIndex = 15
End With
Call Kapitel_G_SpalteB_1
End Sub

Sub Kapitel_G_SpalteB_1()
   Dim c As Range
   Dim bereich As Range
   Dim wks As Worksheet
   Dim wx
   Set wks = Workbooks("Masterprog.xla").Worksheets("Tabelle1")
   wx = wks.Range("BE1")
   Set bereich = ActiveSheet.Range("G2:G" & ActiveSheet.Range("A65536").End(xlUp).Row)
    For Each c In bereich
        'If InStr(c, wx) <> 0 Then c.Offset(0, -5) = "ü"
        If InStr(UCase(c), UCase(wx)) <> 0 Then c.Offset(0, -5) = "ü"
        Next
   End Sub

Wenn ich das Ganze jetzt noch auf die zwei anderen Suchbegriffe, also insgesamt 3 lösen könnte, wäre es super.
Du hast mir auf jeden Fall mit Deiner Lösung sehr geholfen. Dafür möchte ich Dir jetzt nochmals herzlich danken.

Josef


  

Betrifft: AW: mit drei von: hary
Geschrieben am: 04.03.2010 10:53:03

Hallo
dann verbinde diese mit Or. Hier mal Beispiel mit 2 Vergleichen. Begriffe in Spalten 11 und 10.

If InStr(UCase(c), UCase(Cells(1, 11))) <> 0 Or InStr(UCase(c), UCase(Cells(1, 10))) <> 0 Then  _
c.Offset(0, -5) = "ü"

gruss hary


  

Betrifft: AW: mit drei von: Josef
Geschrieben am: 05.03.2010 06:18:05

Hallo hary!

Danke nochmals für Deine Hilfe und Deine Geduld. Jetzt passt alles.

Josef


Beiträge aus den Excel-Beispielen zum Thema "Formel ColorIndex"