I hope someone can help and I hope Ive been clear. Thank you in advance for
your advice. It is much appreciated.

If cell A4 is a particular month (mmm) or my calculation in that field is:
=text(date)(year)(now()),month(now()),0),€�mmm€�

and F4:Q4 are months shown as mmm (i.e F4 is Jul , G4 is Aug , H4 is Sep
etc.)

For example, A4 is showing JAN now but the actual month is Feb (regardless
of the day in February)

I need to count the cells that have a positive value in them for ONLY the
previous 6 months and place that count in the proper row in Col R. So if
its Feb then count G:L or Sep-Jan.

To determine the rows that need to have this count applied to it I have code
in another module called color format that uses the follow to determine which
rows. It is:

' Check length of cell in column A
If Len(cell.Offset(0, -(ncol - 1))) = 4 Then
Count ???????????

Then I need to look only in the cells in the rows for the previous 6 months
and do the following:

If R39:R500 is =5 then background color in col R for that corresponding row
is 4

If R39:R500 is =4 and if at least one cell in F9:Q9 is =$3000 then
background color in col R for that corresponding row is 4

If R39:R500 is =4 and if NO cell is =$3000 then background color in col R
for that corresponding row is 35

If R39:R500 is =3 and if at least one cell in F9:Q9 is =$3000 then
background color in col R for that corresponding row is 35

If R39:R500 is =3 and if NO cell is =$3000 then background color in col R
for that corresponding row is 36

If R39:R500 is =2 and if at least one cell in F9:Q9 is =$3000 then
background color in col R for that corresponding row is 36

If R39:R500 is <=2 and if NO cell is =$3000 then background color in col R
for that corresponding row is 3

If R39:R500 is 0 or BLANK then background color in col R for that
corresponding row is 3

Also if count is 0, Id prefer just a blank with background of 3 instead of 0.

When A4 switches to Feb and the actual month is Mar. I need it to override
the previous background colors and put the new corresponding background
colors in its place.
Another alternative if it makes the vba any easier is in F4:Q4 I have
conditional formatting so that when A4 is JAN, €œJAN€� in L4 has background
highlighted in bright yellow and font black and if not = to A4 then Black
background, white font. So when I open my spreadsheet in February, JAN is
highlighted at L4 not February.

Thanks again for your help.