Shading cells - problem with macro code

QuestionEdit

QUESTION: I tried test using the revised code and got the following message:

Run-time error ¡®1004¡¯: Unable to set the ColorIndex property of the Interior class

When I clicked debug, the following line was highlighted: sh.Cells.Interior.ColorIndex = xlNone

It¡¯s about the 16th line down within the macro code. When I placed my cursor over this flagged line, the following line pops up: sh.Cells.Interior.ColorIndex = -4142

Would you know what's causing this error to occur and what I need to do to resolve this problem.

Stan

ANSWER: Stan,

It sounds like you must have merged cells in your sheet. You can remove the line

sh.Cells.Interior.ColorIndex = xlNone

that just clears shading in the worksheet before putting in the new shading. If you need that, you can do it manually.

---------- FOLLOW-UP ----------

QUESTION: I apologize. The error with the macro was caused by me forgetting to do something. Before running the test, I forgot to unprotect the worksheet. That caused the error to occur when trying to execute the macro.

So after unprotecting the worksheet, I ran another test. The shading of cells worked properly except for one condition - when the date change.

For example, on 9/9/2010 the final row of data for that specific day has 5.555 in cell E56. The next row of data is for 9/10/2010 and has 6.666 in column E57. If the dates had been the same, that would have triggered the code to execute the process to shade in the cell E57 containing 6.666 as well as cells F58 and AG58. But because rows 56 and 57 contained data for different days, no shading of cells is required.

Please let me know how to modify the code to include this logic.

Stan

AnswerEdit

Stan, I previously tested for this situation and found no problems. But in response to your posting,

I put in the exact situation you describe and the code worked fine for me. no cells in rows 56 or 57 were shaded.

this line of code:

Set rE = rE.Offset(1, 0).Resize(rE.Rows.Count - 1, 1)

excludes checking for the first row of a date section. For the date of 9/10/2010, the code starts checking in row 58

So I have no recommended changes in the code since in my tests, it works perfectly. Perhaps you didn't clear shading from the sheet before running the macro and this is residual from when you had the macro shading the row beyond the current row.

See the attached picture. If I didn't test the situation you describe, let me know.