Change Colour

[vmjan02]

I have the code below and just need to work it in correct way

this table below has the colour code in RGB it should fill the colour with respect to RGB , if the cell value is above 70 then RGB colour fill should be (0,176,80) and so on.

any help

1​	109.43​		RGB	0,176,80	Green color	> 70
2​	105.826​		RGB	146,208,80	light green	>55 - <=70
3​	103.195​		RGB	255,0,0	Red	<55
5​	83.479​
6​	105.624​
7​	47.382​
8​	104.02​
9​	111.292​
10​	61.826​
11​	97.132​
12​	109.231​
13​	66.869​
14​	175.715​

Sub fillcolor()

For I = 1 To 15
a = Sheet1.Cells(I + 1, 2) / 71 * 255
c = Sheet1.Cells(I + 1, 2) / 70 * 255
b = Sheet1.Cells(I + 1, 2) / 55 * 255

Sheet1.Shapes.Range(Array("Freeform " & I + 1)).Fill.ForeColor.RGB = RGB(a, c, b)
Next I


End Sub

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[vmjan02]

Hi can you help please.

This code workes all perfect

Sub fillcolorSubsinthousands()
  Dim i As Long
  Dim a As Long
  Dim sRGB As String
  Dim vRGB As Variant
  
  'On Error Resume Next
  For i = 1 To 10
    Select Case Sheet5.Cells(i + 4, 7).Value
      Case Is < 0: sRGB = "255,0,0"
      Case Is > 0: sRGB = "0,176,80"
      Case Else: sRGB = "146,208,80"
    End Select
    vRGB = Split(sRGB, ",") '"146,208,80"
    'Sheet5.Shapes.Range(Array("TextBox" & i)).Fill.ForeColor.RGB = RGB(vRGB(0), vRGB(1), vRGB(2))
    Sheet5.Shapes.Range(Array("TextBox" & i)).TextFrame.Characters.Font.Color = RGB(vRGB(0), vRGB(1), vRGB(2))
    Next i
End Sub

But once i change the column ref from 7 to 11 code don't take the right colour

Sub fillcolorSubsinthousands()
  Dim i As Long
  Dim a As Long
  Dim sRGB As String
  Dim vRGB As Variant
  
  'On Error Resume Next
  For i = 11 To 20  ''(Text box name is change from 11 to 20)
    Select Case Sheet5.Cells(i + 4, 11).Value ''' Here the column is changed
      Case Is < 0: sRGB = "255,0,0"
      Case Is > 0: sRGB = "0,176,80"
      Case Else: sRGB = "146,208,80"
    End Select
    vRGB = Split(sRGB, ",") '"146,208,80"
    Sheet5.Shapes.Range(Array("TextBox" & i)).TextFrame.Characters.Font.Color = RGB(vRGB(0), vRGB(1), vRGB(2))
   Next i
End Sub

 

[Peter_SSs]

Can't see the column & row labels. In Sheet5, what is in G5:G14 and in K15:K24?

 

[vmjan02]

I hope now it seasy

 

[Peter_SSs]

This code is reading values from K15:K24 but there are no values in those cells.

 

[vmjan02]

So if i want to pick the values from K5:K14, what change should be done. for textbox11 to textbox20 what change need to eb done
as textbox11 will pick up value from K5 and do the colour change and so on...

 

[Peter_SSs]

So if i want to pick the values from K5:K14, what change should be done. for textbox11 to textbox20 what change need to eb done
as textbox11 will pick up value from K5 and do the colour change and so on...

Have you tried to work that out for yourself?

At the moment, the code starts at 11 and adds 4 (=15). That is why it reads the first value from row 15 (K15).
Instead of adding 4, what would you need to do to 11 to get the first value from row 5?

 

[vmjan02]

ok, will give it a try

 

[Peter_SSs]

ok, will give it a try

Post back if you do run into more difficulties with it.

 

[vmjan02]

after the change it worked perfect , thanks man.