Formating - Advanced Question / Topic

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

One function we do at work is rank customer satisfaction.
The ranking is first given a number (150,125,100,75,50).
Once the number is a determined specific colors go with
each value -

150 - purple (color index 23)
125 - blue (color index 6).....

I want to create a feature that can take the inputted
value and apply the proper background color to the cell.
Conditional formatting will not work because I have 5
conditions.

Any ideas?? Feel free to email me and I can send you a
spreadsheet example of what I am working with....
 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DropDownRange As Range
Dim ShadedRange As Range

If Target.Cells.Count > 1 Then Exit Sub

Set DropDownRange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
If Intersect(Target, DropDownRange) Is Nothing Then Exit Sub

Set ShadedRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "N"))
With ShadedRange.Interior
Select Case (Target.Value)
Case 1: .ColorIndex = 36
Case 2 To 10: .ColorIndex = 37
Case 11 To 15: .ColorIndex = 38
Case 16 To 25: .ColorIndex = 39
Case 25 To 50: .ColorIndex = 40
Case Is > 50: .ColorIndex = 41
Case Else: .ColorIndex = xlNone
End Select
End With

End Sub

Make changes in Case to meet your needs.
 
Back
Top