Offset range and Sum formula

[Aquamore]

Hi,


I need a line of code that would do the following:

Please Login or Register  to view this content.

I need to add:
if Sum (f.Offset(,-1):f.Offset(,-23)) = 0 or is blank then f.Interior. Color= RGB(100,100,100)
But Then I need it to go to the next column to the right and do it as many times as there are columns in the spreadsheet
Would really appreciate your help!
Thanks!

[noboffinme]

Hi Aquamore,

Can you post the file for an example?

Your range is 'Z1:Z100', any OFFSET with a negative will either go to the left of the selected Cell or above it.

A before & after example would be good.

Cheers

[Danerida]

Assuming your data is on sheet 1:

Please Login or Register  to view this content.

Dan

[Aquamore]

Noboffinme, thanks for looking!
Danerida, you rock!
The only thing- "Exit For" would not work for me I changed it to

Please Login or Register  to view this content.

[Danerida]

Strange, it worked for me.

Did you mean to put Next_Cell: in the outer loop (just before Next i)? If you leave it in the inner loop, it will continue even if the cells in the current range are blank. Checking for the Null string was intended to stop the macro shading blank cells for no reason and wasting time.

Glad to help.

[Danerida]

Strange, it worked for me.

Did you mean to put Next_Cell: in the outer loop (just before Next i)? If you leave it in the inner loop, it will continue even if the cells in the current range are blank. Checking for the Null string was intended to stop the macro shading blank cells for no reason and wasting time.

Glad to help.