Setting pattern color in VBA

[JacquesMaeder]

Hi,
I have a procedure that set / reset the pattern of the currently selected cell which work fine but I would like to set the pattern color too so I added the line in blue:

Public Sub SetReset_Pattern(thePattern, patternColorIndex)

With ActiveSheet
If Selection.Interior.Pattern = thePattern Then
Selection.Interior.Pattern = EMPTY_PATTERN
Else
Selection.Interior.Pattern = thePattern
Selection.Interior.patternColorIndex = patternColorIndex
End If
Selection.Activate
End With
End Sub

but the patternIndexColor is never executed, after setting the pattern the execution quit the procedure without any error. If I exchange the order then it's the pattern which is not set !

Just for try I entered :

Selection.Interior.patternColorIndexxxxx = patternColorIndex

and curiously the compiler does not complain !

Does anyone have an idea what is going on ?

Thank you in avance

 

[Domenic]

What values have been assigned to thePattern and patternColorIndex, and passed to SetReset?

 

[JacquesMaeder]

What values have been assigned to thePattern and patternColorIndex, and passed to SetReset?

I use these value (actually to set a pattern for week-end and public holidayson planning sheet).

Public Const EMPTY_PATTERN = -4142
Public Const JF_PATTERN = -4124
Public Const JF_PATTERN_ColorIndex = 14


I actually got the value form existing cells formatted the conventional way using this function:

Sub pick_pattern()
Dim ip As Long
Dim ipc As Long

ip = Selection.Interior.Pattern
ipc = Selection.Interior.Pattern
End Sub

The problem does not seems to came from the value themselves but to the fact that only one of the 2 lines of code is executed, either setting the pattern or the pattern color index but not both.

 

[JacquesMaeder]

The right Pick-Pattern function!

Sub pick_pattern()
Dim ip As Long
Dim ipc As Long

ip = Selection.Interior.Pattern
ipc = Selection.Interior.patternColorIndex
End Sub

 

[Domenic]

Can you explain in words the logic that you would like to use?

 

[JacquesMaeder]

Can you explain in words the logic that you would like to use?

I use a worksheet to set the employees monthly planning. To make it short the columns of the planning correspond to days 1 to 31 and I have macros that set the weekend-days with a gray pattern.
I also want to mark public holidays with a different pattern but I would like it to be colored (a green pattern for instance). Since public holidays date cannot be easily calculated I have set a button that call the macro which set /reset the corresponding pattern. Now this work fine as long as it only set the pattern but not for the pattern color; if in the macro I exchange the order of the lines that set the pattern to set the pattern color first this line is executed but not the second. Whatever I do, only one line is executed and the debugger just quit the procedure without any warning.