assigning different background colours in a range

[MJKelly]

Hi,

If I want to loop through a range (A110) and look for different
values (A, B, C, D etc), how would I assign a background colour
according to the value?

Would it be a select case option?

Also, instead of stating the colour as FFFFFF etc could you use
standard colour names such as Red, Blue, Black etc?

kind regards,
Matt

 

[Chip Pearson]

You could try something like the following:

Sub ColorCells()
Dim Rng As Range
Const RED = 3
Const GREEN = 4
Const BLUE = 5
Const YELLOW = 6

For Each Rng In Range("A1:C5").Cells
Select Case UCase(Rng.Text)
Case "A"
Rng.Interior.ColorIndex = RED
Case "B"
Rng.Interior.ColorIndex = GREEN
Case "C"
Rng.Interior.ColorIndex = BLUE
Case "D"
Rng.Interior.ColorIndex = YELLOW
''''''''''''''''''''''''''
' other Cases go here
''''''''''''''''''''''''''
Case Else
'''''''''''''''
' do nothing
'''''''''''''''
End Select
Next Rng
End Sub

To get the colors available, run the following code. The row number of each
color is the ColorIndex that is assigned to the Interior of a cell. E.g.,
row 6 has a yellow fill, so 6 is the ColorIndex that refers to yellow. Set
up constants for all the colors you want to use, as shown in the example
code.

Sub ListColorIndexs()
Dim WS As Worksheet
Dim N As Long
Set WS = Worksheets.Add
For N = 1 To 56
Cells(N, 1).Interior.ColorIndex = N
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

While I was dragging my feet preparing an answer for this I see that Chip has
already answered. However, one other thing you might need is to be able to
remove the interior colorindex:-

Rng.Interior.ColorIndex = xlColorIndexNone