Skip i'r prif gynnwys

Sut i newid lliw siâp yn seiliedig ar werth celloedd yn Excel?

Gall newid lliw siâp yn seiliedig ar werth celloedd penodol fod yn dasg ddiddorol yn Excel, er enghraifft, os yw gwerth y gell yn A1 yn llai na 100, mae lliw y siâp yn goch, os yw A1 yn fwy na 100 ac yn llai na 200, bydd y mae lliw siâp yn felyn, a phan fydd A1 yn fwy na 200, mae lliw y siâp yn wyrdd fel y dangosir y screenshot canlynol. I newid lliw y siâp yn seiliedig ar werth cell, bydd yr erthygl hon yn cyflwyno dull i chi.

newid lliw siâp 1

Newid lliw siâp yn seiliedig ar werth celloedd gyda chod VBA

swigen dde glas saeth Newid lliw siâp yn seiliedig ar werth celloedd gyda chod VBA

Gall y cod VBA isod eich helpu chi i newid lliw siâp yn seiliedig ar werth cell, gwnewch fel a ganlyn:

1. Cliciwch ar y dde ar y tab dalen rydych chi am newid lliw siâp, ac yna dewiswch Gweld y Cod o'r ddewislen cyd-destun, yn y popped allan Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch a gludwch y cod canlynol i'r gwag Modiwlau ffenestr.

Cod VBA: Newid lliw siâp yn seiliedig ar werth celloedd:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160704
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value < 100 Then
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
        ElseIf Target.Value >= 100 And Target.Value < 200 Then
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow
        Else
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
        End If
    End If
End Sub

newid lliw siâp 2

2. Ac yna pan fyddwch chi'n nodi'r gwerth yng nghell A1, bydd lliw'r siâp yn cael ei newid gyda gwerth y gell fel y gwnaethoch chi ei ddiffinio.

Nodyn: Yn y cod uchod, A1 yw'r gwerth cell y byddai eich lliw siâp yn cael ei newid yn seiliedig ar, a'r 1 Oval yw enw siâp eich siâp wedi'i fewnosod, gallwch eu newid i'ch angen.

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (22)
Rated 4 out of 5 · 1 ratings
csi
csi
This comment was minimized by the moderator on the site
how would be the VBA code to paint several autoforms according to a range of data?
the values are in column B and I have 20 autoforms.
Reply
0
0
Emilly
Emilly
This comment was minimized by the moderator on the site
Como faço para para variar as cores da forma se minha opções for em formato de texto, como "Sim" e "Não"?
Reply
0
0
skyyang
skyyang     Emilly
This comment was minimized by the moderator on the site
Hello, Emilly
To solve your problem, please apply the below code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "Yes" Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
Else
If Range("A1") = "No" Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
End If
End If
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
Neil
Neil     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

I tried the original VBA code and got it to work, although it would not activate actively when the cell changed. Today the code is not working and I also tried your more simplistic code and still did not work. The only thing that changed is that I copied the worksheet which contained the code that worked. Would this then result in it not working?
Reply
0
0
Roberto tovar
Roberto tovar
This comment was minimized by the moderator on the site
Hola, excelente ejemplo.
Pero como seria si tengo una forma y quiero ir coloreado poco a poco dependiendo del valor ejemplo:
Si el valor es 50%
Seia mitad roja y mitad verde
Pero que se vaya llenando según el porcentaje vaya aumentando
Reply
0
0
Rated 4 out of 5
Max
Max
This comment was minimized by the moderator on the site
I am new to VBAs and am struggling with something. I need to have 9 different cells A1-A9 change the colour of 9 different objects. Objects are cubes 1-9. Just to clarify, each cell is to change just one object A1-Cube 1, etc. Red if it fails to meet the value and green if it exceeds the value. The pass/fail value can change so instead of having the value in the VBA I need it to reference cell A10 which has the pass/fail value. Any chance someone could through out a sample code for me to work with.

Thanks
Reply
0
0
mnsosa
mnsosa
This comment was minimized by the moderator on the site
Great solution! How can I do if I have more than 1 oval in the worksheet which the colors change according to the value input say in A1, B1,C1? Thanks in advance for your reply! 
Reply
Report
1
0
MandyZhou
MandyZhou     mnsosa
This comment was minimized by the moderator on the site
Hello mnsosa,Glad to help. Please copy and paste the below VBA code into the blank Module window.
Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.Please see my screenshot. Hope it can help. Have a nice day.Sincerely,Mandy
View Attachments (1 / 5)
Reply
Report
0
0
María Noel
María Noel
This comment was minimized by the moderator on the site
¿Cómo hacemos si tenemos más de 1 Oval en la hoja de trabajo cuyos colores cambian de acuerdo con el valor ingresado, por ejemplo, en A1, B1, C1...? Mil gracias por su ayuda!

Reply
0
0
MandyZhou
MandyZhou     María Noel
This comment was minimized by the moderator on the site
Hello María Noel,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Ryan
Ryan
This comment was minimized by the moderator on the site
Hi... excellent solution... but how do I apply it to multiple shapes based on the corresponding values of a range of cells. Many thanks in advance for your help.
Reply
Report
0
0
MandyZhou
MandyZhou     Ryan
This comment was minimized by the moderator on the site
Hello Ryan,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Cesare
Cesare
This comment was minimized by the moderator on the site
How do I make the private sub to read the result from the AVERAGE(C1,C5,C9) calculation?

Sub only works with numeric values; any thoughts and suggestions are greatly appreciated.
Reply
0
0
MandyZhou
MandyZhou     Cesare
This comment was minimized by the moderator on the site
Hello Cesare,How are you? I notice that the VBA code can work with the AVERAGE(number, number...) calculation. But the trick is that every time you change the values in the calculation, you need to double-click the formula in the cell to make the VBA work again. 
For example, In cell A1, after we input the formula is =AVERAGE(C2:D3), the VBA works and changes the color of the shape accordingly. Please see screenshot 1. <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">C2:D3, the returned result in cell A1 changes, but the shape color hasn't been changed yet. In this case, we need to double click the formula in cell A1 to make the VBA work. Then the shape color will be changed accordingly. Please see screenshots 2 and 3.</span>
View Attachments (2 / 5)
Reply
Report
0
0
Steve A
Steve A
This comment was minimized by the moderator on the site
Thanks for this which is really useful.

I now want to use it with a pivot table on another worksheet which controls the data on the sheet with the shapes that I want to change colour. However, when I change the selection on the pivot table the data on the worksheet with the shapes is updated but the code does not run so the shapes do not change colour

If I manually change the values the code runs and the colour of the shapes is updated.

Question: what do i need to add to the code above to allow it to run automatically?
Reply
0
0
Yasir
Yasir
This comment was minimized by the moderator on the site
How can this be applied if you have multiple shape in the same worksheet?
Reply
0
0
MandyZhou
MandyZhou     Yasir
This comment was minimized by the moderator on the site
Hello Yasir,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations