Solved: Macro Recorder Code fails

[bushy]

EXCEL 2007

Used Macro Recorder to do Conditional Formatting. The code generated to change the font color fails, the code for the cell interior executes correctly. The error is from this line below: .Color = -16752384
The error message is as follows:
Run-time error 1004:
Application-defined or object-defined error

The Code:

Columns("H:H").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="CONNECT", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty

With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With

Selection.FormatConditions(1).StopIfTrue = False

[Simon Lloyd]

Are you trying to use html color codes? take a look here http://msdn.microsoft.com/en-us/library/cc296089(office.12).aspx

[Bob Phillips]

Why not just ColorIndex?

[bushy]

To Simon: I do not know if I am using HTML Color Codes. The code sample is exactly what the MACRO Recorder created.

To xld: I don't have the background to answer your question.

If the created Macro code cannot work, does anyone have a suggestion on how it should be done?

[YasserKhalil]

Try this

Cells.FormatConditions.Delete
Columns("H:H").Select
With Selection
.FormatConditions.Add Type:=xlTextString, String:="Connect", TextOperator:=xlContains
.FormatConditions(1).Interior.ColorIndex = 3
End With

[bushy]

To Yasser: Thank you for the suggestion, it's probably due to my ignorance but I read you code and do not see how it will cure my problem. However, I will try it. Was I clear that the code failure is with the the Font Color and not with the cells interior color? The line of code for the interior works correctly, it is the font color that is the failure. Does you code make the font a dark green agains a light green interior?

[YasserKhalil]

I tried your code and it works well
I just abbreviated the code as folllows

Cells.FormatConditions.Delete
Columns("H:H").Select
With Selection
.FormatConditions.Add Type:=xlTextString, String:="Connect", TextOperator:=xlContains
.FormatConditions(1).Interior.Color = 13561798
.FormatConditions(1).Font.Color = -16752384
End With
Try it and tell us the result

[Simon Lloyd]

Failing that why not attach the faulty workbook and we can help directly with that :)

[mdmackillop]

To see the colorindex choices, run the following

Sub Colours()
Dim i&
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

[Simon Lloyd]

MD if you don't mind me jumping in with a slightly enhanced version :)
Sub colors56()
'57 colors, 0 to 56
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, il As Long
Dim str0 As String, str As String
Dim Arr
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Cells(i + 1, 2).Font.ColorIndex = i
Cells(i + 1, 2).Value = "[Color " & i & "]"
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
'Excel shows nibbles in reverse order so make it as RGB
str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
'generate a column in the HTML table
Cells(i + 1, 3) = "#" & str
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"

Next i
'Add column captions
Arr = Array("Interior", "Font", "HTML", "R", "G", "B")
Rows("1:1").Insert Shift:=xlDown
For il = 0 To 5
Cells(1, il + 1).Value = Arr(il)
Next il
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Or a tad simpler



Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlChartCIPeriwinkle = 17 '-----------------------------
xlChartCIPlum = 18 ' chart colours
xlChartCIIvory = 19 '
xlChartCILightTurquoise = 20 '
xlChartCIDarkPurple = 21 '
xlChartCICoral = 22 '
xlChartCIOceanBlue = 23 '
xlChartCIIceBlue = 24 '
xlChartCIDarkBlue = 25 '
xlChartCIPink = 26 '
xlChartCIYellow = 27 '
xlChartCITurquoise = 28 '
xlChartCIViolet = 29 '
xlChartCIDarkRed = 30 '
xlChartCITeal = 31 '
xlChartCIBlue = 32 '-----------------------------
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum

[bushy]

I tried your code and it works well
I just abbreviated the code as folllows

Cells.FormatConditions.Delete
Columns("H:H").Select
With Selection
.FormatConditions.Add Type:=xlTextString, String:="Connect", TextOperator:=xlContains
.FormatConditions(1).Interior.Color = 13561798
.FormatConditions(1).Font.Color = -16752384
End With Try it and tell us the result

It worked perfectly, Thank you so much.

[bushy]

Thanks to everyone, the suggestions were all very valuable to me.