Preskoči na glavno vsebino

Kako ohraniti oblikovanje celic, medtem ko se sklicuje na druge celice lista?

Na splošno celica ohranja vrednost celice le med sklicevanjem na drugo celico, vendar v tem članku predstavljam kodo VBA, ki ohranja vrednosti celic in oblikovanje, medtem ko se sklicuje na drugo celico, vrednosti celic in oblikovanje pa se spreminjajo, ko se referenčna celica spreminja, kot spodaj prikazan posnetek zaslona.
doc ob sklicevanju ohrani formatiranje 1

Obdržite oblikovanje celic, medtem ko se na drugo celico sklicujete z VBA


Obdržite oblikovanje celic, medtem ko se na drugo celico sklicujete z VBA

Če želite opraviti to nalogo, morate zagnati spodnjo kodo.

1. Omogočite delovni zvezek, ki ga uporabljate, pritisnite Alt + F11 tipko za omogočanje Microsoft Visual Basic za uporabo okno in dvokliknite ime lista, v katerega boste postavili referenčno celico Projekt-VBAProjekt podokno za prikaz praznega skripta. V tem primeru se želim sklicevati na celico A1 v Sheet1. Oglejte si posnetek zaslona:
doc ob sklicevanju ohrani formatiranje 2

2. Spodnjo kodo prilepite v skript in v kodo VBA določite sklice na celice, kot jih potrebujete.

VBA: ohranite oblikovanje in vrednosti, medtem ko se sklicujete na drugo celico

Private Sub Worksheet_Activate()
'UpdatebyExtendoffice20101024
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then              'Range("A1") the reference cell
        Target.Copy
        ActiveWorkbook.Sheets("Sheet2").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme
                    'Range("B1")the cell linked to reference cell,ActiveWorkbook.Sheets("Sheet2")the sheet which contains linked cell
        Application.CutCopyMode = False
        Target.Select
    End If
    Application.EnableEvents = True
End Sub

Opombe: A1 je referenčna celica, B1 in 2 Sheet je celica, ki jo želite povezati z referenčno celico in ohraniti vrednosti in oblikovanje z A1 v Shee1.

Potem, ko spremenite vrednosti ali oblikovanje v celici A1 v Sheet1, bo celica B1 v Sheet2 spremenjena med dvakratnim klikom na referenčno celico.


Štej po barvah

V nekaterih primerih imate lahko vrsto iztočnic z več barvami in kar želite, da štejete / seštevate vrednosti glede na isto barvo, kako lahko hitro izračunate?
z Kutools za Excel's Štej po barvah, lahko hitro izvedete veliko izračunov po barvah in ustvarite tudi poročilo o izračunanem rezultatu.
število dokumentov glede na barvo

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, I would like to keep the format of my reference cell. However, even with your VBA it doesn't work. Can you help me please?
I would like my B2 cell of my sheet 1 to be my reference cell for my C2 cells of sheets 2,3,4,5,6,7.
My document is a list of people, therefore I will have several cells of references not the continuation.
Thanks in advance
This comment was minimized by the moderator on the site
No tienes uno que sea dentro del mismo archivo
This comment was minimized by the moderator on the site
Thanks for sharing this. I have a range of cells in WorksheetA, Sheet1, Range G3:G3000 that I want to reference in WorksheetB, Sheet1, Range G3:G3000. I need WorksheetB, Sheet1, Range G3:G3000 to display both the value and the format of the referenced cells in WorksheetA, Sheet1, G3:G3000. Is there a VBA script that will allow for this?
This comment was minimized by the moderator on the site
Thanks for sharing this. Is there a VBA script that allows for referencing a range of cells in one workbook and then displaying the value and format of the referenced cells in a different workbook?
This comment was minimized by the moderator on the site
Thank you so much for the script. Can you tell me if there is a way for the linked cell to update without having to double click on the reference cell? Thank you!
This comment was minimized by the moderator on the site
hello, I believe this may be just what I am looking for. I need the referenced text to have the same colors and features from the referenced page. The only thing different about mine is that I am pulling from a larger range. I have never used VBA before so I wanted to confirm before I did changes. I am pulling from a document (sheet 2) onto (sheet 1) document. I have used IF formulas to do this, so it returns a value from 3 different columns and they are not in a row. The columns are F,H,J. Could you please help me figure out how I can make this work?
This comment was minimized by the moderator on the site
I am dealing with similar issue. I have a formula in column C, which takes value from the same row, column A. (But only IF B3 is not x AND A3 is not empty):

=IF(B3="x";"y";IF(A3="";"z";A3))

Cells of column A look like this: OK 2019_12_03
But "OK" is in bold format. I would like to keep this format.
I want this for hundreds of cells, so clicking or writing a script for each one of them is undesirable. Any ideas if such feature exists? I'd appreciate some kind of "WITHFORMAT()" function that I could put in the formula, so the following formula would keep the original format:

=IF(B3="x";"y";IF(A3="";"z";WITHFORMAT(A3)))
This comment was minimized by the moderator on the site
Sorry, your problem is a bit complex, I do not understand clearly.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations