Setting Color of Range based on adjacent cell color

This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.

I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.

The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.

What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.

Here is the code I am using to set the color of the cells in Column A:

Please Login or Register  to view this content.

If I can figure this out, I'll next try taking a similar approach with applying borders on the value change.

Many thanks in advance.

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

Hello JimE,

Welcome to the Forum!

In VBA it is seldom necessary to have to select an object before an action is undertaken. I have rewritten your macro to access the cells directly.

Please Login or Register  to view this content.

Similarly,

Please Login or Register  to view this content.

Hello Leigh, Thank you for taking the time to look at this. I have been unsuccessful in running this code as I keep getting a Run Time 1004 error; application or object defined error. I think it is hanging up on this line:

For Each Cell In Rng.Rows(1).Offset(-1, 0)

The code Shg provided runs very well, but I would also like to give this one a shot if the fix is simple.

I should note, there will be additional columns added to the worksheet which should not get colored when the macro runs. I'd like to just color the rows in columns A through E, regardless if there is data in columns F through J.

Regards,
Jim[/SIZE]

Originally Posted by shg

Similarly,

This works very nicely. You make something so foreign seem so simple.

Is there a way to make it so the last column is fixed, say column E, instead of using the used range? There will be additional columns added to the data for calculations which should not get colored. I'd like to just color the rows in columns A through E, regardless if there is data in columns F through J.

Many thanks,
Jim[/SIZE]

Is there a way to make it so the last column is fixed, say column E, ...

Please Login or Register  to view this content.