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.

See:

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

"Roopa" wrote:

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.

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???

"Toppers" wrote:

See:

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

"Roopa" wrote:

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.

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

"Roopa" wrote:

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???

"Toppers" wrote:

See:

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

"Roopa" wrote:

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.

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.




"Vivek Shah" wrote:

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

"Roopa" wrote:

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???

"Toppers" wrote:

See:

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

"Roopa" wrote:

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.

Thanks a lot Vivek. I tried this option.

"Vivek Shah" wrote:

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

"Roopa" wrote:

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???

"Toppers" wrote:

See:

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

"Roopa" wrote:

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.