Excel 2002: Gleiche Hyperlinks finden

Question

Hallo, wer weiß Rat?
Ich habe eine Zahlenreihe in Spalte A, die laufend erweitert wird.
Jede Zahl ist mit einem Hyperlink auf das Tabellenblatt 2 versehen.
A1-A3 verweist jeweils auf Zelle E1
A4-A5 verweist jeweils auf Zelle E2
A6-A10 verweist jeweils auf Zelle E3
usw.
Nun möchte ich bei Ergänzungen der Zahlenreihe in Spalte A, dass die Nachbarzellen (Spalte B) der Hyperlinks mit gleichen Zielzellen sich abwechselnd färben.
Also B1-B3 gelb
B4-B5 rot
B6-B10 wieder gelb, usw.

Danke für Lösungsvorschläge!

reinri

Answer 1

Hallo Mr. K.

Sieht sehr gut aus!!!
Ein Problemchen hab ich noch beim Drücken von Strg+Enter:
Da das neue Linkziel nicht immer um genau eine Zelle nach unten "rutschen" soll, sondern eine unterschiedliche Anzahl von Zellen nach unten springen soll, wäre es gut, wenn ich mit Strg+Enter nur das Hyperlink-Eingabefeld öffne und dann per Hand meine neue Link-Zielzelle eingeben kann.

Gibt es da auch noch eine Lösung?

Danke schon mal und LG

reinri

Answer 2

Hallo reinri,

kein Problem. Auch das ist noch recht simpel umzusetzen. Du
musst nur den Code wie folgt ändern.

Private Sub Worksheet_Change(ByVal Target As Range)

Const Gelb = 65535
Const Orange = 39423
Const Rot = 255

If Target.Row > 1 Then
If Target.Offset(-1, 0).Hyperlinks.Count > 0 Then
If ActiveCell.Address = Target.Address Then
If Application.Dialogs(xlDialogInsertHyperlink).Show Then
Link = ActiveCell.Hyperlinks(1).SubAddress
End If
Else
Link = Target.Offset(-1, 0).Hyperlinks(1).SubAddress
End If

p = InStr(1, Link, "!")
If p > 0 Then
Blatt = Left(Link, p - 1)
Zelle = Right(Link, Len(Link) - p)
Else
Blatt = Target.Parent.Name
Zelle = Link
End If

If ActiveCell.Address = Target.Address Then
'Zelle = Sheets(Blatt).Range(Zelle).Offset(1, 0).AddressLocal
Target.Offset(0, 1).Interior.Color _
= IIf(Target.Offset(-1, 1).Interior.Color _
= Gelb, Orange, Gelb)
Else
Target.Offset(0, 1).Interior.Color _
= Target.Offset(-1, 1).Interior.Color
End If

If Zelle <> "" Then Target.Hyperlinks.Add Target, "", Blatt & "!" &
Zelle _
, Range(Zelle).AddressLocal, Target.Text
End If
End If

End Sub

Gruß Mr. K.

Answer 3

Besten Dank Mr.K.
Es funktioniert wie gewünscht.
Ich habe jetzt meine Farben angepasst.

Const hellgrau = 14540253
Const dunkelgrau = 12632256

Dabei ist hellgrau aber kein Teil vom Hintergrundfarbenfeld sondern nur im Zellenformatierungsdialogfeld unter "Muster" zu finden.
Dunkelgrau ist Füllfarbe grau-25% vom Hintergrundfarbenfeld.

Dadurch bringt die Entertaste in der Spalte B zwar dunkelgrau wenn dunkelgrau in der darüberliegenden Zeile ist, aber schwarz wenn hellgrau in der darüberliegenden Zeile ist.

Bei Strg+Enter für das Öffnen des Hyperlinkdialogfeldes klappt es aber mit beiden Farben (hellgrau und dunkelgrau).

Ich habe ein Makro aufgezeichnet zum Öffnen des Zellenformatierungsdialogfelds "Muster":

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Ich vermute, dass es mit den Textteilen "Interior.Color" in deinem Makro zu tun hat. Weil hellgrau dort nicht erkannt wird, wird der Farbcode "0" interpretiert und "0" ist eben die Nummer von der Farbe schwarz.

Kann man mein aufgezeichnetes Makro so einbauen, dass auch hellgrau gefunden bzw. erkannt wird?

Danke noch mal für deine Geduld!

LG reinri

Answer 4

Hallo rainri,

ColorIndex bezieht sich nur auf die Position in der Farbpalette. Die darin enthaltene Farbe kann durchaus abweichen. Deshalb habe ich die Color-Eigenschaft verwendet.

zumindest in Excel 2010 funktioniert das Ganze auch mit hell- und dunkelgrau. Kann ja heute Abend nochmal mit 'ner alten Excel-Version testen. Wüsste aber nicht, warum das dort nicht funktionieren sollte.

Einzige Änderung ist die Wortersetzung Gelb durch Hellgrau und Orange durch Dunkelgrau.

Private Sub Worksheet_Change(ByVal Target As Range)

Const Hellgrau = 14540253
Const Dunkelgrau = 12632256

'Const Rot = 255

If Target.Row > 1 Then
If Target.Offset(-1, 0).Hyperlinks.Count > 0 Then
If ActiveCell.Address = Target.Address Then
If Application.Dialogs(xlDialogInsertHyperlink).Show Then
Link = ActiveCell.Hyperlinks(1).SubAddress
End If
Else
Link = Target.Offset(-1, 0).Hyperlinks(1).SubAddress
End If

p = InStr(1, Link, "!")
If p > 0 Then
Blatt = Left(Link, p - 1)
Zelle = Right(Link, Len(Link) - p)
Else
Blatt = Target.Parent.Name
Zelle = Link
End If

If ActiveCell.Address = Target.Address Then
'Zelle = Sheets(Blatt).Range(Zelle).Offset(1, 0).AddressLocal
Target.Offset(0, 1).Interior.Color _
= IIf(Target.Offset(-1, 1).Interior.Color _
= Hellgrau, Dunkelgrau, Hellgrau)
Else
Target.Offset(0, 1).Interior.Color _
= Target.Offset(-1, 1).Interior.Color
End If

If Zelle <> "" Then Target.Hyperlinks.Add Target, "", Blatt & "!" _
& Zelle, Range(Zelle).AddressLocal, Target.Text
End If
End If

End Sub

Gruß Mr. K.

Answer 5

Wenn ich's recht bedenke, meine ich, mich zu erinnern, dass damals tatsächlich nur die in der Farbpallette hinterlegten Farben dargestellt werden konnten. Werd' mich heut Abend nochmal dazu melden. Auch da findet sich garantiert eine schnelle Lösung.

Kannst ja zwischenzeitlich mal eine beliebige Farbposition über Extras-Optionen auf dem Registerblatt Farben an die die dunkelgraue Farbe anpassen. Vielleicht reicht das ja schon aus.

L.G. Mr. K.

Answer 6

Über Extras-Optionen auf dem Registerblatt Farben habe ich keinen Erfolg.

Mir fällt auf:
Wenn ich in der Symbolleiste die Farbpalette öffne und mit der Maus auf zB rot zeige, erscheint ein kleines Textfeld mit dem Wort "rot".
Bei meinem speziellen Hellgrau erscheint aber das Wort "Farbschema".

Gruß reinri

Answer 7

Hallo raini,

ich hab mir das jetzt nochmal angesehen und sehe was du meinst.
Offenbar haben die alten Excel-Versionen hier einen Bug. Das Auslesen
der Color-Eigenschaft gibt nicht den Farbcode der Benutzerdefinierten
Farbe wieder, sondern den Code der Farbe die standardmäßig an
dieser Stelle wäre. Das ist mir bislang so auch noch nicht aufgefallen.
Allerdings ist bekannt, dass das alte Excel beim Zuweisen für die Zelle
eine der 56 Farben aus der Palette auswählt, die dem angegebenen
Farbcode am ehesten entspricht. Das war damals durchaus
beabsichtigt um Speicherplatz und Arbeitsspeicher zu schonen. In
neueren Excel-Versionen sind nun aber bis zu 65536 Farben in einer
Mappe gleichzeitig darstellbar.

Somit unterscheidet sich bei dir Color nicht wirklich von ColorIndex.
Trotzdem ist Color generell besser, weil die Farbcodes zwischen den
Excelversionen gleich bleiben, während Colorindex in Excel 2010 unter
gleicher Zahl eine Andere Farbe wiedergibt.

Für dein Problem gibt es zwei simple Möglichkeiten. Wenn du über
Extras Optionen einem Platz in der Palette eine andere Farbe
zugewiesen hast, schau dir im Ändern-Fenster mal die Registerkarte
"Anpassen" an. Dort kannst du für deine gewählte Farbe die RGB-Werte
auslesen. Somit umgehst die die Zuweisung des Farbcodes 0 und
weist stattdessen z.b. Const hellgrau = RGB(234, 234, 234) zu.

Alternativ (und noch einfacher) kannst du natürlich hier ausnahmsweise
im Code auch Color durch Colorindex ersetzen und statt des Farbcode
die Palettenposition (0 bis 56) auslesen, bzw. setzen. Beide Varianten
sind besser (und schöner) als diese hässlichen Muster, welche
heutzutage nicht mehr wirklich verwendet werden, sondern nur noch aus
Gründen der Abwärtskompatibilität zur Verfügung stehen.

Übrigens: Die Zahl 1 ist der Color-Code für Schwarz. 0 steht für
Automatisch, was hier nur zufällig auch Schwarz ist. Automatisch
bedeutet, dass Schrift mit dieser Farbe, auf sehr dunklem Hintergrund
u.U. selbstständig weiß erscheint. Soweit ich mich erinnere, funktioniert
das aber nur in Word. In Excel konnte ich das so noch nicht bestätigen.

Über die Bezeichnung "Farbschema" brauchst du dir keine Gedanken zu
machen. Hier siehst du lediglich, dass du eine Benutzerfarbe gewählt
hast. Man kann aber natürlich nicht allen 2 Millionen möglichen Farben
einen Namen geben.

L.G. Mr. K.

Answer 8

Hier noch eine mögliche Variante über Colorindex. Hellgrau habe ich der
Position hellrosa (unten links) zugewiesen.

Private Sub Worksheet_Change(ByVal Target As Range)

Const Hellgrau = 38
Const Dunkelgrau = 15

'Const Rot = 255

If Target.Row > 1 Then
If Target.Offset(-1, 0).Hyperlinks.Count > 0 Then
If ActiveCell.Address = Target.Address Then
If Application.Dialogs(xlDialogInsertHyperlink).Show Then
Link = ActiveCell.Hyperlinks(1).SubAddress
End If
Else
Link = Target.Offset(-1, 0).Hyperlinks(1).SubAddress
End If

p = InStr(1, Link, "!")
If p > 0 Then
Blatt = Left(Link, p - 1)
Zelle = Right(Link, Len(Link) - p)
Else
Blatt = Target.Parent.Name
Zelle = Link
End If

If ActiveCell.Address = Target.Address Then
'Zelle = Sheets(Blatt).Range(Zelle).Offset(1, 0).AddressLocal
Target.Offset(0, 1).Interior.ColorIndex _
= IIf(Target.Offset(-1, 1).Interior.ColorIndex _
= Hellgrau, Dunkelgrau, Hellgrau)
Else
Target.Offset(0, 1).Interior.ColorIndex _
= Target.Offset(-1, 1).Interior.ColorIndex
End If

If Zelle <> "" Then Target.Hyperlinks.Add Target, "", Blatt & "!" _
& Zelle, Range(Zelle).AddressLocal, Target.Text
End If
End If

End Sub

Answer 9

Nochmals Hallo Mr.K.

Besten Dank für die ausführlichen Erklärungen und Hilfe!!!
Klappt echt super!
Ich werde die Color.Index-Variante nehmen, da ich die Datei ohnehin nur mit meiner Excelversion betreibe.

Herzliche Grüße

reinri