Skip to main content

Conas luach a athrú bunaithe ar dhath na cille in Excel?

Tá an t-alt seo ag caint ar luach cille a athrú go huathoibríoch bunaithe ar a dath cúlra in Excel. Mar shampla, má tá cealla ag líonadh le dath cúlra dearg sa roghnú, ansin líon na cealla seo le huimhir 1, agus le haghaidh na gcealla dath cúlra gorm, líon uimhir 0.

Athraigh luach bunaithe ar dhath na cille le cód VBA


Athraigh luach bunaithe ar dhath na cille le cód VBA

Is féidir leat an cód VBA thíos a rith chun luach a athrú bunaithe ar dhath na cille in Excel. Déan mar a leanas le do thoil.

1. Roghnaigh an raon a theastaíonn uait chun luachanna a athrú bunaithe ar dhath an chúlra, ansin brúigh Eile + F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil, ansin cóipeáil agus greamaigh an cód VBA thíos i bhfuinneog an Mhodúil.

Cód VBA: Athraigh luach bunaithe ar dhath na cille in Excel

Sub ChangeValueBasedOnCellColor()
    Dim rg As Range
    Dim xRg As Range
    Set xRg = Selection.Cells
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .Interior.Color
                Case Is = 255 'Red
                    .Value = 1
                Case Is = 15773696 'Blue
                    .Value = 0
            End Select
        End With
    Next
    Application.DisplayAlerts = False
End Sub

3. Brúigh an F5 eochair chun an cód a rith, ansin is féidir leat a fheiceáil go bhfuil na cealla dearga go léir sa raon roghnaithe líonta le huimhir 1, agus líontar na cealla gorma le huimhir 0 mar a thaispeántar thíos.

 

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there a way to do this, but have it automatically go through multiple files?
For context, questionnaires were sent to a couple hundred facilities, their MC answer selected corresponds to a color indicating score (e.g. green is good, red is bad), and change the content of the cell (with a process like in this thread) to something that R or another software could read, and then use that to generate automatic reports. I could go through each of these files with this macro (or a completely different idea), but that would take a long time.
This comment was minimized by the moderator on the site
The code works fine for me, but I would need to rewrite the code to fit colours specific for my document. But I don´t know what code "my colours" have, anyone who know where on can find codes for other colours?
This comment was minimized by the moderator on the site
I came across this lucky strike which was helpful but don't know where to find a full directory!
http://cdn-0.access-excel.tips/wp-content/uploads/2015/08/excel_vbcolor_10.png 
This comment was minimized by the moderator on the site
How do you run it, i run but nothing seems to happen
This comment was minimized by the moderator on the site
You can replace colors to values by simply using Find and Replace function. Go to the format function on the right side of the Find and Replace dialog box and then under "Fill" you can select the color that you have. Then on the "replace with" just write the value you want.
This comment was minimized by the moderator on the site
is it possible to adjust this code based on the cells conditional formatting rule?
This comment was minimized by the moderator on the site
Excel seems to crash whenever I try to run the VBA code. Seems like a bust.
This comment was minimized by the moderator on the site
Good day,
The code works well in my case. Can you tell me your Excel version? Thank you for your comment.
This comment was minimized by the moderator on the site
I'm in the same boat, trying to run this but the code seems to get hung. Excel 2016.
This comment was minimized by the moderator on the site
I'm looking to give a cell a name based on the colour within one worksheet. i.e. if a cell is red then it gets named "name", if it is orange then it gets names "surname" etc.



What code would need to change to have the range as the whole sheet/tab and a name instead of a value?
This comment was minimized by the moderator on the site
What changes to the code would be needed if you wanted this to apply to the text color of a cell, rather than the cell color?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations