Expand on current VBA procedure

[pbt]

Hi folks,

I have the follwing code that will turn the ajoining cell to a particular color based on the Month of a date that is formated 1/12/06.

I need to expand on this code so it will also look at the Year. This spreedsheet will run into the next year so just looking at the month will not do. Of course I would change the color index for the next year to something else.

Sub ColorByMonth()
    Dim rCell As Range
    Application.ScreenUpdating = False
    Application.Calculate
    On Error GoTo Xit
    For Each rCell In Range("j2:j" & Range("j65536").End(xlUp).Row)
        Select Case Month(rCell)
            Case 1
                rCell.Offset(0, 1).Interior.ColorIndex = 36
            Case 2
                rCell.Offset(0, 1).Interior.ColorIndex = 42
            Case 3
                rCell.Offset(0, 1).Interior.ColorIndex = 39

This continues for all twelve months (Case 12)

Any help would be greatly appreciated.

Thanks
h.h.

 

[pbt]

Took the formula but gave me a value of 0 in the cell. There are numbers in the range to sum but it's not doing it.

h.h.

 

[jindon]

try

=Sum(If(Year(WEBB!$J$2:$J$1470)=2006,If(Month(WEB!$J$2:$J$1470)=1,WEB!$K$2:$K$1470)))

confirm with Ctrl + Shift + Enter

 

[jindon]

harry,

why don't you ask the forum for the forumla?

it is quite clear that you want to sum the column adjacent to the column
that have specific year and month values....

 

[pbt]

That works fine Jindon

The only problem that I still have is anytime there is a cell change, be it in the columns that we have been working on or say column "A' where there is text, the sheet calculates, which I know is not going to make any of the users happy.

Is there any possible way to fix this. I guess I'm stuck on how the sheet was working before we expanded with the year 2007.

I think we might be setting a record (or close) ~ over 70 replies, 8 pages, almost 700 views.

If it's easier, I could send you a shortened version of the file before we started and what we have now.

Thanks
h.h.

 

[jindon]

Currently, I still have problem with my PC and can not use Excel

Sorry, but that is why it took so long and I was asking to test the code..
and sorry for the typos, cuase I'm writing the code directly to the thread.

Regarding recalculating the sheet, what kind of other forumla are you using in the sheet?

 

[pbt]

Sorry I went to bed after my last post.

Opened the file and this thread this morning to see your reply.

Seams like I do not have the calculation problem as describe earlier.

Will do some more testing (after I get some chores done around the house) and let you know the outcome.

h.h.

 

[jindon]

OK

And if the purpose of coloring cells was to calculate the sum, you don't need it to anyway...

 

[pbt]

Good Morning to you, Jindon (5:45PM for me)

Got everything working. Was able to get rid of the UDF's, a couple of macro buttons, and a few modules and I was able to add two more columns for it to work with. Everything is working great

Yes! you are right.

And if the purpose of coloring cells was to calculate the sum, you don't need it to anyway...

You and I know this, but the boss likes to see pretty colors on his reports. And the users have been doing this for so long (with so many human error mistakes) , I am going to have to leave the colors.

I have just one more question, if it's not to much. (like this hasn't been drawn out to much already) ray:

I had asked this a few pages back:

Is it possible to make this robust. Meaning, instead of refering to the year 2006 that it will refer to the current Year

I tried to do something with this line, but nothing seems to work:

If Year(r.Value) <> 2006 Then myColor = myColor + 1

I'm just worried about somebody having to go into the Editor to change the "2006" next year.

This sheet will be used from year to year and all rows except probally the last 100 will carry forward to the next year. In this case 100 rows of "2007" will start "2008".

Also a similar copy of this workbook will be used in three different branches of the company, all on the East coast. I am located on the West coast(or I might get fired one day)

Long winded ~ hope you got the idea

Thanks
h.h.

 

[jindon]

How about

myColor = myColor + Year(r.Value) - 2006

myColor will be added difference of 2006 and Year(r.Value)
and will be subtracted difference when year(r.Value) is smaller than 2006

 

[pbt]

Don't know if you understood what I was after, or if I just don't understand your reply.

What I have right now works fine for this year. Not even worried about 2005 because all data for 2005 is purged.

Come Jan 2007 about 900 (estimated) rows of 2006 data that have a date in column "U" (which is the determining column to justify deletion) will be deleted leaving the remaining 2006 data. (2006 in columns other then "U") "U" is blank.

As these rows get paid (now in 2007) the date will be entered in "U"

Come Jan 2008, same process is done (using 2007 dates in "U"). So now we start 2008 with rows that have dates of 2007 in columns other then "U". "U" is blank, but will fill with a 2008 dates.

h.h.