How to colourfill a cell with more than 3 conditions?

[Roopa]

I have prepared a worksheet to identify the skill levels of people across
many technologies. I need to colour code the cells according to the skill
levels. I could have used conditional formatting, but the problem is that I
have to colour code the cells for more than three conditions.

 

[Toppers]

See:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Roopa]

Thanks a lot. The information was really helpful to me. Just one more
clarification... I complete my worksheet by installing this add-in. But, when
I share it with others will they be able to use the worksheet without any
problems since they would not have installed this add-in???

 

[Vivek Shah]

Use this VBA Code

Private Sub Color_Click()

Dim I As Integer
Dim Cellvalue As Integer


For I = 1 To 10
Cellvalue = Cells(I, "A").Value
Cells(I, "A").Activate

Select Case Cellvalue

Case Is = 1
ActiveCell.Interior.Color = RGB(10, 10, 0)

Case Is = 2
ActiveCell.Interior.Color = RGB(50, 50, 0)

Case Is = 3
ActiveCell.Interior.Color = RGB(90, 90, 0)

Case Is = 4
ActiveCell.Interior.Color = RGB(130, 130, 0)

Case Is = 5
ActiveCell.Interior.Color = RGB(170, 170, 0)

Case Is = 6
ActiveCell.Interior.Color = RGB(210, 210, 0)

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

End Select

Next I

End Sub

 

[Toppers]

Yes ... they will need the addin.

Here is some alternative VBA code (courtesy of Bob Phillips) which will
allow you to code more than 3 conditions:


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Roopa]

Thanks a lot Vivek. I tried this option.

Use this VBA Code

Private Sub Color_Click()

Dim I As Integer
Dim Cellvalue As Integer


For I = 1 To 10
Cellvalue = Cells(I, "A").Value
Cells(I, "A").Activate

Select Case Cellvalue

Case Is = 1
ActiveCell.Interior.Color = RGB(10, 10, 0)

Case Is = 2
ActiveCell.Interior.Color = RGB(50, 50, 0)

Case Is = 3
ActiveCell.Interior.Color = RGB(90, 90, 0)

Case Is = 4
ActiveCell.Interior.Color = RGB(130, 130, 0)

Case Is = 5
ActiveCell.Interior.Color = RGB(170, 170, 0)

Case Is = 6
ActiveCell.Interior.Color = RGB(210, 210, 0)

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

End Select

Next I

End Sub