How do I set a colour to 4 cells based on the value of a cell

[Guest]

I want to assign a colour to a group of 4 cells (ie a1 to a4) based on the
colour that is typed into another cell (ie d1). It could be that I need the
option of using 10 colours.
eg if d1 was "green" then a1-a4 would be coloured green.

 

[Gord Dibben]

Andy

For greater than 3 colors(4 if use default no color) you will need VBA.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set rng1 = Intersect(Target, Range("D1"))
If rng1 Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Set rng2 = Range("A1:A4")
'Determine the color
Select Case rng1.Value
Case Is = "green": Num = 10 'green
Case Is = "black": Num = 1 'black
Case Is = "blue": Num = 5 'blue
Case Is = "magenta": Num = 7 'magenta
Case Is = "orange": Num = 46 'orange
Case Is = "red": Num = 3 'red
'add more cases as you wish
End Select
rng2.Interior.ColorIndex = Num
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Note: Data Validation>List can be used to enter the value into D1.


Gord Dibben Excel MVP