changing cell color on value conditions gives wrong color

Question

I*m trying to change cell color on value conditions, situation is there are different posts on my work where they pick pcs to put in a pallet

on every station we calculate hours and pcs per hour = pcs:hours=solution

example : ratings are if 120pcs or more per hour is cell color green between : 119pcs and 80pcs a hour cell color is orange less than 80pcs a hour cell color is red

now for many posts my code is working but for some posts it gives me the wrong color why i didn't find

I don't know what is wrong with it

I post the code for Vman all workstations have this some code but just changing the conditions values Vman is 120pcs a hour like i told before other stations can be 2250, 100, 350, 300

and so on most of the stations gives the right result of color

is the code for Vman

If ComboBox1.Text = "Vman" Then
   Range("ah10").Font.Color = vbBlack
   Range("ah10").Font.Bold = True
   Range("ah10").Font.Color = vbBlack
   'grijs-Gray
   If Range("ah10").Value = "" Then
      Range("ah10").Interior.Color = RGB(128, 128, 128)
      Range("ah10").Font.Color = vbBlack
      'rood-Red
   ElseIf Range("ah10").Value < "80" Then
      If Range("ah10").Value > "" Then
         Range("ah10").Interior.Color = vbRed
         Range("ah10").Font.Color = vbWhite
      End If
      'oranje-Orange
   ElseIf Range("ah10").Value >= "80" Then
      If Range("ah10").Value < "120" Then
         Range("ah10").Interior.Color = RGB(255, 153, 0)
      End If
      'groen-Green
   ElseIf Range("ah10").Value >= "120" Then
      Range("ah10").Interior.Color = RGB(146, 208, 80)
   End If
End If

If ComboBox1.Text = "Vman 3" Then
   Range("ah10").Font.Color = vbBlack
   Range("ah10").Font.Bold = True
   Range("ah10").Font.Color = vbBlack
   'grijs-Gray
   If Range("ah10").Value = "" Then
      Range("ah10").Interior.Color = RGB(128, 128, 128)
      Range("ah10").Font.Color = vbBlack
      'rood-Red
   ElseIf Range("ah10").Value < "80" Then
      If Range("ah10").Value > "" Then
         Range("ah10").Interior.Color = vbRed
         Range("ah10").Font.Color = vbWhite
      End If
      'oranje-Orange
   ElseIf Range("ah10").Value >= "80" Then
      If Range("ah10").Value < "120" Then
         Range("ah10").Interior.Color = RGB(255, 153, 0)
      End If
      'groen-Green
   ElseIf Range("ah10").Value >= "120" Then
      Range("ah10").Interior.Color = RGB(146, 208, 80)
   End If
End If

I don't find the solution for the code the most stations are working and gives me the good color but some don't.

Check: Color the cell manually and then test what `Range("AH10").Interior.Color` returns on a machine where it works and where it doesn't. — shrivallabha.redij, Feb 23 '19 at 09:19

Christofer Weber · Accepted Answer · 2019-02-23T12:42:47.453

Is there a reason to have the code twice?

Also, the reason why it doesn't work, seems to be because of your value being inside "quotation marks".

As to why I use .value2 instead of .value refer to this post:
What is the difference between .text, .value, and .value2?

I would also suggest referring to the range with a variable. This way, you only have to change it once, instead of 10 different locations in the code.

Try this

Dim pieces As Range
Set pieces = Range("AH10") 
If ComboBox1.Text = "Vman" Or ComboBox1.Text = "Vman 3" Or ComboBox1.Text = "Vman 4" Then
    pieces.Font.Color = vbBlack
    pieces.Font.Bold = True
    If pieces.Value2 = "" Then                              'grijs-Gray
        pieces.Interior.Color = RGB(128, 128, 128)
    ElseIf pieces.Value2 < 80 Then                          'rood-Red
        pieces.Interior.Color = vbRed
        pieces.Font.Color = vbWhite
    ElseIf pieces.Value2 > 79 And pieces.Value2 < 120 Then  'oranje-Orange
        pieces.Interior.Color = RGB(255, 153, 0)
    ElseIf pieces.Value2 > 119 Then                         'groen-Green
        pieces.Interior.Color = RGB(146, 208, 80)
    End If
End If

When repeating like that, you can use a With ... End With instead of, or in conjunction with the variable.

If ComboBox1.Text = "Vman" Or ComboBox1.Text = "Vman 3" Or ComboBox1.Text = "Vman 4" Then
    With Range("AH10")
        .Font.Color = vbBlack
        .Font.Bold = True
        If .Value2 = "" Then                               'grijs-Gray
            .Interior.Color = RGB(128, 128, 128)
        ElseIf .Value2 < 80 Then                           'rood-Red
            .Interior.Color = vbRed
            .Font.Color = vbWhite
        ElseIf .Value2 > 79 And pieces.Value2 < 120 Then   'oranje-Orange
            .Interior.Color = RGB(255, 153, 0)
        ElseIf .Value2 > 119 Then                          'groen-Green
            .Interior.Color = RGB(146, 208, 80)
        End If
    End With
End If

this line is not working is it the Or status? If ComboBox1.Text = "Vman" Or "Vman 3" Then if i do Vman without or its working also cel color is correct so there are 3 stations with the same values to set color of the cell there is Vman , Vman 3 and Vman 4 is it possible to cet 1 code for these 3 stations without the OR — Jürgen Lambrecht, Feb 23 '19 at 10:37
Yeah, sorry, that was my fault. I forgot that you need the whole condition after the `Or`. Updated the code. — Christofer Weber, Feb 23 '19 at 12:07
Using .Value2 in this scenario is not necessary, it just makes your code ugly. — z32a7ul, Feb 23 '19 at 13:58
@z32a7ul Experts say: "Value2 really should be the default, and is definitely the one to use 99% of the time." I'm just trying to make it a habit. But if you find it ugly, I wont stop you from removing the number two, it makes no difference here. — Christofer Weber, Feb 23 '19 at 14:43

changing cell color on value conditions gives wrong color

1 Answers1