Excel 2003 VBA Conditional Formatting Question

[SkippyPB]

I have a perplexing problem with some VBA code I wrote to do a mass
change of the conditionl formatting in a spreadsheet. It does the
formatting but the color of the pattern isn't correct and I can't seem
to get it right.

Here's a snippet of the code I am doing:

Sub SetCutFormatting()
'
'

'
Sheets("Totals").Activate
ActiveSheet.Range("A38").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=cutk3<>""k3"""
With Selection.FormatConditions(1).Interior
.ColorIndex = 18
.Pattern = xlLightUp
End With

ActiveSheet.Range("A39").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=cutk4<>""k4"""
With Selection.FormatConditions(1).Interior
.ColorIndex = 18
.Pattern = xlLightUp
End With

The cells selected end up with the thin diagonal stripe pattern and
the color is plum. However, it is too dark.

I found that if I do this manually and press Clear from the Patterns
dialog box and then set the pattern and color it comes out correctly
(a much lighter shade) I don't see a way to mimic that in VBA. Can
someone help me out here please?

TIA.

(o o)
-oOO--(_)--OOo-
Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and
never amounted to much.
The second one, naturally, became known as the
lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Jim Cone]

Your formula doesn't make sense to me; I used "=a4<>k4" to test.

Your code attempts to set the color of the cell interior not the color of the pattern...
Change: .ColorIndex = 18 to .PatternColorIndex = 18
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ShadeData
(free trial - excel addin)

..
..
..

"SkippyPB" <[email protected]>
wrote in message
I have a perplexing problem with some VBA code I wrote to do a mass
change of the conditionl formatting in a spreadsheet. It does the
formatting but the color of the pattern isn't correct and I can't seem
to get it right.

Here's a snippet of the code I am doing:

Sub SetCutFormatting()
Sheets("Totals").Activate
ActiveSheet.Range("A38").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=cutk3<>""k3"""
With Selection.FormatConditions(1).Interior
.ColorIndex = 18
.Pattern = xlLightUp
End With

ActiveSheet.Range("A39").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=cutk4<>""k4"""
With Selection.FormatConditions(1).Interior
.ColorIndex = 18
.Pattern = xlLightUp
End With

The cells selected end up with the thin diagonal stripe pattern and
the color is plum. However, it is too dark.

I found that if I do this manually and press Clear from the Patterns
dialog box and then set the pattern and color it comes out correctly
(a much lighter shade) I don't see a way to mimic that in VBA. Can
someone help me out here please?

TIA.
-oOO--(_)--OOo-
Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and
never amounted to much.
The second one, naturally, became known as the
lesser of two weevils.
Remove nospam to email me.
Steve

 

[SkippyPB]

Your formula doesn't make sense to me; I used "=a4<>k4" to test.

Your code attempts to set the color of the cell interior not the color of the pattern...
Change: .ColorIndex = 18 to .PatternColorIndex = 18

Thank you for the response. The formula works it is just that I used
a named data element which occurs on another tab of the workbook.
That's why it didn't make sense to you.

The change worked perfectly.

Regards,
(o o)
-oOO--(_)--OOo-
Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and
never amounted to much.
The second one, naturally, became known as the
lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve