Thread: charts and VBA

ha ha ha.....since you asked....sort of! I am having difficulty on a project right now, perhaps you can help?

The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work.

In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)

Simon


Dim r As Integer
Dim c As Integer
Dim cellnum As Integer
Dim mike As Signature
Dim simon As Balloon



Private Sub CommandButton1_Click()



For c = 7 To 38

Worksheets(3).Cells(3, c).Value = Worksheets(1).Cells(3, c).Value 'names

Next c

For c = 2 To 6
For r = 4 To 150

Worksheets(3).Cells(r, c).Value = Worksheets(1).Cells(r, c).Value 'units

Next r
Next c


'start row
r = 11
'start column
c = 8

'clean the page
For r = 11 To 150 'last active row
For c = 8 To 40
Cells(r, c).Value = ""
Next c
Next r

'Check values on sheet 1 and transfer to requirement sheet

For r = 11 To 131
For c = 8 To 38


If Worksheets(1).Cells(r, c) > 0 Then 'looking for non zero cells


If Worksheets(1).Cells(r, c).Value = "1" Then Worksheets(3).Cells(r, c).Value = "Urgent"
If Worksheets(1).Cells(r, c).Value = "2" Then Worksheets(3).Cells(r, c).Value = "Coaching"
If Worksheets(1).Cells(r, c).Value = "3" Then Worksheets(3).Cells(r, c).Value = "Training"
If Worksheets(1).Cells(r, c).Value = "4" Then Worksheets(3).Cells(r, c).Value = "Test"
If Worksheets(1).Cells(r, c).Value = "5" Then Worksheets(3).Cells(r, c).Value = "N/R"
If Worksheets(1).Cells(r, c).Value = "na" Then Worksheets(3).Cells(r, c).Value = "N/A"
If Worksheets(1).Cells(r, c).Value = "NA" Then Worksheets(3).Cells(r, c).Value = "N/A"
If Worksheets(1).Cells(r, c).Value = "n/a" Then Worksheets(3).Cells(r, c).Value = "N/A"
If Worksheets(1).Cells(r, c).Value = "N/a" Then Worksheets(3).Cells(r, c).Value = "N/A"
If Worksheets(1).Cells(r, c).Value = "N/A" Then Worksheets(3).Cells(r, c).Value = "N/A"
If Worksheets(1).Cells(r, c).Value = "n/A" Then Worksheets(3).Cells(r, c).Value = "N/A"


Else: Worksheets(3).Cells(r, c).Value = "Supv"


End If
Next c
Next r

'Check dates on sheet 1 and transfer to requirement sheet

For r = 4 To 10
For c = 7 To 38

If Worksheets(1).Cells(r, c).Value = IsDate(False) Then

If Worksheets(1).Cells(r, c).Value = False Then Worksheets(3).Cells(r, c).Value = "URGENT"

End If

Next c
Next r

'trying to color comments below following the cell population two commands above

Worksheets(3).Activate
Range("G4:AL131").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="URGENT", Formula2:="Urgent"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="Training", Formula2:="TRAINING"
Selection.FormatConditions(2).Interior.ColorIndex = 44
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="Coaching", Formula2:="COACHING"
Selection.FormatConditions(3).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="Test", Formula2:="TEST"
Selection.FormatConditions(4).Interior.ColorIndex = 43
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="N/A", Formula2:="N/R"
Selection.FormatConditions(5).Interior.ColorIndex = 7
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="SUPV", Formula2:="Supv"
Selection.FormatConditions(6).Interior.ColorIndex = 33


End Sub