Put color to cells

[Manhamanha]

I have a table with changing lengths of the rows and columns. At the end of each row/column there is a cell with the sum. I want to put some color to it and make some additional formatting - have some borders around the main table and different colors for the empty and non-empty cells. I have the following code:

Please Login or Register  to view this content.

which gives me error 438 - Object doesn't support this property or method. I can't find any errors in the code and I guess there is a problem with the range.Could you help me?

[royUK]

You don't need code to colour the cells - use Conditional Formatting - http://www.excel-it.com/excel_condit...formatting.htm

I also find it much easier to have the totals above the data table. They are then easier to view and can be written to reflect the change in number of rows. SubTotal is also a good choice because you can filter & get a total of filtered lines

[JBeaucaire]

Maybe a little faster this way:

Please Login or Register  to view this content.

Meanwhile, I'd probably use conditional formatting so this happened on its own in realtime.

[Manhamanha]

As this is part of a bigger project I want it all written with macroses. Conditional formating is not an object for me. As for the sums - I need them to be placed at the end of the rows/columns because this is one of the requirements.

[royUK]

Try this, alter the colours to suit

Please Login or Register  to view this content.

[Manhamanha]

Yes, this puts color in the main table and bounderies on the whole range. How can I put bounderies on the table only and leave the blank cells without any?

[royUK]

Do you mean around the outer edge?

Please Login or Register  to view this content.

[Manhamanha]

I mean I want to have visible bounderies around the cells that contain any value an have no bounderies around the ones that are blank.

[JBeaucaire]

Yes, this puts color in the main table and bounderies on the whole range. How can I put bounderies on the table only and leave the blank cells without any?

I think that's what mine in post #3 already does for you. Give that a shot.

[royUK]

just a slight amendment needed

Please Login or Register  to view this content.

[Manhamanha]

This works great Just one more thing if you have time.. how can I put a different color in the last row and column - the ones with the sums.

[royUK]

Is your range fixed because you hard code it?

[Manhamanha]

No it is not. The rows and columns length vary depending on a number that is entered by user. After that they are filled with special numbers and the sums are calculated. The values I had as range to check in were just example.

[royUK]

I would replace it with UsedRange or set the range with the code

Please Login or Register  to view this content.

[Manhamanha]

It really looks better this way. What about the different color of the last row/column - is it possible?
And do you have any idea why does this throw error 400 ?

[royUK]

My last code did that

Please Login or Register  to view this content.

[Manhamanha]

Well, it doesn't.. it throws 400 and colors all the 'non-empty' cells with one and the same color.

[Manhamanha]

Ok, it's getting too complicated to explain, so here is the whole code:

Please Login or Register  to view this content.

I have changed the filling color in your method.
I'm attaching a few pictures too. The first one with the error I get. The second one is how the table looks now and the last one is how I want it to look.
I hope it's clear now - take a look if you have time..

Thanks anyway a lot

[royUK]

Attach the workbook. The final code that I posted worked like that for me.

[Manhamanha]

Hi, I managed to deal with it by deleting one of the lines from your code

.SpecialCells(xlCellTypeConstants).Interior.ColorIndex = xlNone

It seems that the problem is with those "SpecialCells". At least now it puts the color as I want it to be and gives no errors. As for the blank spaces I'll figure it out later. Thanks againg for the great help, I really appreciate your efforts