Trying to change cell color and shade (marco) in Excel 2003

[Amesville]

Hope someone can help me here...

Working on a MS Excel 2003 project that reads data from a sheet and uses it to create a summary report. Each row in the data sheet is used to populate one cell on one row in the report; there are about 20 rows per project in the data sheet, each one gets a date value copied to the report sheet based on a Task Description. When all 20 lines of a project are read the next project begins, and we start over on the report sheet on the next line down.

The Date in each cell may be either previous to, the same or later than a plan date that was a target. The client wants the color of each cell to be green, yellow or red depending on the relationship above to the plan date. No problem, I can do that:

Pseudocode:
Sheet("X").cell(X,Y).Interior.colorindex = n.

But there is also a value which states if the line item is completed or not, and if completed the client want to Shade the cell in addition to the color. I figured that out too (can't remember the statement off the top of my head but it worked.)

But here's the thing: I can't seem to do both color and shade a cell in my macro. If I color it it knocks out the shading. If I shade it first it knocks out the color.

Is there a way (in Excel 2003) that I can set the color and shade in a cell using VBA code in a macro?

PS: I understand this actually works with no problem in later versions of Excel but I don't have access to those in this case.

Thanks

Craig

 

[SkipVought]

...and your recorded code???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Amesville]

Skip - I was just getting to it, and in the process I figures out what needed to be done.

With Sheets("Pict").Cells(iRptRowNum, iPrjColNum).Interior

.pattern = xlGray50
.PatternColorIndex = xlAutomatic
.ColorIndex = 4 ' Green

End With

Thank you, that helped a lot. I will try to use the Macro Recorder more often for questions like this.

Craig