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.

Lisa, you have some quite detailed requirements here, but it's
not clear (to me at least) exactly what it is that you need help with.
This might explain why you haven't had any replies yet. The basic
idea seems to be:

If (some condition is met) Then
(set background colour to X)
ElseIf (some other condition is met) Then
(set background colour to Y)
....... several more tests....
End If

If you could clarify what it is that you need to know I'm sure
someone will be able to help. Is it testing the conditions?
Setting the colours? Something else?


BTW the formula you give of
=text(date)(year)(now()),month(now()),0),"mmm"
should presumably be
=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0),"mmm")

Andrew


Lisa wrote:
I hope someone can help and I hope I've 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, I'd 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.

Thanks Andrew. The requirements do seem complicated but you are write about
the basic "if some condition is met then.... Basically if a cell in F4:Q4
is current month & if col A has a 4 digit # in it then count the # of cells
in the previous 6 months in that row that have a positive # and put that
figure in the corresponding cell in col R. Then apply the if then statements
I have below to apply a background color in the corresponding cell in col R.
Does that help? Thank you.

"Andrew Taylor" wrote:

Lisa, you have some quite detailed requirements here, but it's
not clear (to me at least) exactly what it is that you need help with.
This might explain why you haven't had any replies yet. The basic
idea seems to be:

If (some condition is met) Then
(set background colour to X)
ElseIf (some other condition is met) Then
(set background colour to Y)
....... several more tests....
End If

If you could clarify what it is that you need to know I'm sure
someone will be able to help. Is it testing the conditions?
Setting the colours? Something else?


BTW the formula you give of
=text(date)(year)(now()),month(now()),0),"mmm"
should presumably be
=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0),"mmm")

Andrew


Lisa wrote:
I hope someone can help and I hope I've 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, I'd 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.