From colour code to RGB in this macro?

[jberg123]

How can I modify this code to use RGB(x,y,z) code instead? Thanks

Private Sub Worksheet_Calculate()
Dim icolor As Integer, c As Range
For Each c In Range("G21:G45,I21:I45,K21:K45")
Select Case c.Offset(, 1)
Case 1: icolor = 4
Case 2: icolor = 35
Case 3: icolor = 44
Case 4: icolor = 6
Case 5: icolor = 2
End Select
c.Interior.ColorIndex = icolor
Next c
End Sub

 

[VoG]

Try

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("G21:G45,I21:I45,K21:K45")
    Select Case c.Offset(, 1).Value
        Case 1: c.Interior.Color = RGB(0, 255, 0)
        Case 2: c.Interior.Color = RGB(204, 255, 204)
        Case 3: c.Interior.Color = RGB(255, 204, 0)
        Case 4: c.Interior.Color = RGB(255, 255, 0)
        Case 5: c.Interior.Color = RGB(255, 255, 255)
    End Select
    Next c
End Sub

 

[MickG]

Try:-

With Range("F1")
    .Interior.Color = RGB(250, 250, 0)
    .Font.Color = RGB(0, 255, 0)
End With

Mick

 

[jberg123]

Try

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("G21:G45,I21:I45,K21:K45")
    Select Case c.Offset(, 1).Value
        Case 1: c.Interior.Color = RGB(0, 255, 0)
        Case 2: c.Interior.Color = RGB(204, 255, 204)
        Case 3: c.Interior.Color = RGB(255, 204, 0)
        Case 4: c.Interior.Color = RGB(255, 255, 0)
        Case 5: c.Interior.Color = RGB(255, 255, 255)
    End Select
    Next c
End Sub

Thank you, worked brilliantly!

Another one, how do I add additional arguments to the cases?

E.g. I'd like to change font colour as well. I tried

...
Case 1: c.Interior.Color = RGB(0, 255, 0)
c.Font.Color = RGB(0,0,0)
Case 2: c.Interior.Color = RGB(204, 255, 204)
...

but it didn't work.

Many thanks

 

[VoG]

Try like this

Case 1: c.Interior.Color = RGB(0, 255, 0): c.Font.Color = RGB(0, 0, 0)

 

[ScottR]

Or on multiple lines by removing the colon

Case 1
   c.Interior.Color = RGB(0, 255, 0)
   c.Font.Color = RGB(0, 0, 0)

 

[jberg123]

Thanks guys, most helpful!

 

[snb_]

Private Sub Worksheet_Calculate()
  For Each c In Range("G21:G45,I21:I45,K21:K45")
    c.interior.color= RGB(Choose(c.Offset(, 1), 0, 204, 255, 255, 255), Choose(c.Offset(, 1), 255, 255, 204, 255, 255), Choose(c.Offset(, 1), 0, 204, 0, 0, 255))
  next
End Sub

But simpler seems:

Private Sub Worksheet_Calculate()
  For Each c In Range("G21:G45,I21:I45,K21:K45")
    c.interior.colorindex= Choose(c.Offset(, 1),4,35,44,6,2)
  next
End Sub

<!-- / message -->