Select Case Range

[Faygin]

I am trying to examine a whole range of cells.
And depedning on which number the cell has in it, it should have a colouring.

Select Case Range("T2:AD900").Value

Case 0
ActiveCell.Interior.Color = xlNone

Case 1
ActiveCell.Interior.Color = RGB(255, 153, 0)

Case 2
ActiveCell.Interior.Color = RGB(51, 204, 204)

Case 3
ActiveCell.Interior.Color = RGB(255, 204, 204)

Case 4
ActiveCell.Interior.Color = RGB(255, 255, 153)

Case 5
ActiveCell.Interior.Color = RGB(204, 255, 255)

Case 6
ActiveCell.Interior.Color = RGB(255, 204, 0)

Case 7
ActiveCell.Interior.Color = RGB(192, 192, 192)

Case 8
ActiveCell.Interior.Color = RGB(150, 150, 150)

Case 9
ActiveCell.Interior.Color = RGB(0, 128, 128)

Case 10
ActiveCell.Interior.Color = RGB(205, 204, 255)

Case 14
ActiveCell.Interior.Color = RGB(205, 204, 255)

Case Else
ActiveCell.Interior.Color = RGB(0, 0, 0)

End Select

I get a type mismatch error.
It seems like its something wrong with: Select Case Range("T2:AD900").Value

Because of i change to Select Case Range("T2").Value and just test 1 cell it seem to work.

Made some other attempts as well, but then all cells get coloured in the first criteria being met.

Please advice! Thank you for your help.

 

[Georgiboy]

Have you considered conditional formatting for the job instead?

 

[nemmi69]

You call only test each cell at a time, so try

Sub Bbb()
Dim CellRange As Range
Dim CCell As Range

Set CellRange = Range("T2:AD900")

For Each CCell In CellRange.Cells
    Select Case CCell.Interior.Color
    
    Case 0
    CellRange.Interior.Color = xlNone
    
    Case 1
    CellRange.Interior.Color = RGB(255, 153, 0)
    
    Case 2
    CellRange.Interior.Color = RGB(51, 204, 204)
    
    Case 3
    CellRange.Interior.Color = RGB(255, 204, 204)
    End Select
Next CCell

End Sub

 

[Faygin]

Have you considered conditional formatting for the job instead?

Actually there is a "classic" conditional formatting in the background that creates the numbers in the cells.
But the problem i have encountered when using conditional formatting is that i need to export the outcome of the formatting (the interior colour) but the internal colour is connected to the formatting so i gave that one up.

 

[Georgiboy]

There are ways to mimic the CF in a range, below is one example, it may not be the best solution to your problem but thought i would share it anyway:

Sub Keep_CF()
    Dim ws As Worksheet
    Dim rRng As Range, rCell As Range

    Set ws = Sheets("Sheet1")
    Set rRng = ws.Range("A1:C20")

    For Each rCell In rRng
        With rCell
          .Font.FontStyle = .DisplayFormat.Font.FontStyle
          .Interior.Color = .DisplayFormat.Interior.Color
        End With
    Next rCell

    rRng.FormatConditions.Delete
End Sub

 

[Faygin]

You call only test each cell at a time, so try

Sub Bbb()
Dim CellRange As Range
Dim CCell As Range

Set CellRange = Range("T2:AD900")

For Each CCell In CellRange.Cells
    Select Case CCell.Interior.Color
   
    Case 0
    CellRange.Interior.Color = xlNone
   
    Case 1
    CellRange.Interior.Color = RGB(255, 153, 0)
   
    Case 2
    CellRange.Interior.Color = RGB(51, 204, 204)
   
    Case 3
    CellRange.Interior.Color = RGB(255, 204, 204)
    End Select
Next CCell

End Sub

Sweet Jesus! That is the solution! You made my day!
Thank you so much for the help, much appreciated!

 

[nemmi69]

No problem, glad to make things easier

 

[Faygin]

Sub Formatera_Celler()

Dim CellRange As Range
Dim CCell As Range

Set CellRange = Range("T2:AD900")

For Each CCell In CellRange.Cells
    Select Case CCell.Interior.Color ' Test the value of the cell?
    
    Case 0
    CellRange.Interior.Color = xlNone
    
   Case 1
   CellRange.Interior.Color = RGB(0, 204, 255)
    
   Case 2
   CellRange.Interior.Color = RGB(204, 255, 255)
    
   Case 3
   CellRange.Interior.Color = RGB(0, 128, 128)
    
  Case 4
  CellRange.Interior.Color = RGB(204, 255, 204)
    
  Case 5
  CellRange.Interior.Color = RGB(255, 255, 153)
    
  Case 6
   CellRange.Interior.Color = RGB(255, 153, 204)
    
   Case 7
  CellRange.Interior.Color = RGB(255, 153, 0)
    
  Case 8
  CellRange.Interior.Color = RGB(150, 150, 150)
    
  Case 9
  CellRange.Interior.Color = RGB(51, 153, 102)
    
  Case 10
 CellRange.Interior.Color = RGB(0, 128, 0)
    
 Case 11
 CellRange.Interior.Color = RGB(204, 255, 255)
    
 Case 12
 CellRange.Interior.Color = RGB(192, 192, 192)
    
  Case 13
 CellRange.Interior.Color = RGB(255, 255, 0)
    
  Case 14
  CellRange.Interior.Color = RGB(102, 102, 153)
    
    End Select
Next CCell

Set MyRange = Range("T2:AD900")
MyRange.FormatConditions.Delete

****, i celebrated too early!
Every cell has a value from 1 to 14 could i just test the value and if the value is "the case" then change interior color?
So all 1´s have one colour, all 2´s another one and so on.

There is a conditional formatting that have already put colours in the cell prior to this code.
But the last thing i do is to remove format conditions and when i do that, i expected the coloring from this code to stay, but it goes away aswell.

My first thought was to change " Select Case CCell.Interior.Color" to Select Case CCell.Value but first attempts didnt go as expected.

Please advice!

 

[nemmi69]

Yep, that would work

 

[Faygin]

Dim CellRange As Range
Dim CCell As Range

Set CellRange = Range("T2:AD300")

For Each CCell In CellRange.Cells
    Select Case CCell.Value
    
 Case 0
 CCell.Interior.Color = xlNone
 CCell.Value = ""
    
  Case 1
  CCell.Interior.Color = RGB(0, 204, 255)
    
 Case 2
 CCell.Interior.Color = RGB(204, 255, 255)
    
   Case 3
   CCell.Interior.Color = RGB(0, 128, 128)
    
  Case 4
  CCell.Interior.Color = RGB(204, 255, 204)
    
 Case 5
CCell.Interior.Color = RGB(255, 255, 153)
    
Case 6
 CCell.Interior.Color = RGB(255, 153, 204)
    
  Case 7
  CCell.Interior.Color = RGB(255, 153, 0)
    
  Case 8
  CCell.Interior.Color = RGB(150, 150, 150)
    
 Case 9
 CCell.Interior.Color = RGB(51, 153, 102)
    
 Case 10
CCell.Interior.Color = RGB(0, 128, 0)
    
Case Is > 10
 CCell.Interior.Color = xlNone
    
    End Select
Next CCell

This did the job, thank you for pointers and help!